Mysql – search for something in the first 1000 records in the table

MySQL

If I do the following MySQL query

select * from table where name Like '%john%' limit 5;

that mean the query will search all the mysql database table and return only the top 5 results that match the query where condition.

BUT, what if want to just search the top 1000 records in the database table that match the where condition and return only the first 5 rows , because I have 1,000,000 records in the table and i just want to search in the first 1000 records, what should i use?

this is the wanted query in human readable:

search the top 1000 records from the table which have the name like john and return only the top 5 records that match.

what is the corresponding in MySQL

Best Answer

You could use something like this:

SELECT *
FROM (
  SELECT *
  FROM your_table
  ORDER BY ...
  limit 1000
) s
WHERE name LIKE '%john%'
ORDER BY ...
LIMIT 5

Suggestion 1

How about (untested)

SELECT designation, count(*)
  FROM designations 
 GROUP BY designation ORDER BY COUNT(*) DESC LIMIT 9

UNION SELECT "Others" designation, COUNT(*) 
        FROM designations
       WHERE designation NOT IN (
               SELECT designation
               FROM designations 
               GROUP BY designation 
               ORDER BY COUNT(*) DESC 
               LIMIT 9 )

Suggestion 2

This one is tested. It uses MySQL-specific variables. It's efficient.

SET @n=0;

SELECT IF(row_number<10, designation,'Others') name,
       sum(c) AS occurances
FROM
  (SELECT designation, c, @n:=@n+1 row_number
   FROM
     (SELECT designation,
             count(*) c
      FROM designations
      GROUP BY designation
      ORDER BY count(*) DESC) t1) t2
GROUP BY IF(row_number<10,row_number,10);

Mysql – Finding violations of the symmetry constraint

To find the rows, use a left outer join:

select 
    a.Friend1ID, a.Friend2ID, b.Friend1ID, b.Friend2ID 
from
    Friends a left join Friends b 
        on (a.Friend1ID=b.Friend2ID and a.Friend2ID=b.Friend1ID)
where 
    b.friend1ID IS NULL ;

Best Answer

Related Solutions

Mysql – Fetch first 9 records and make one more record which will be as “Others” in MySQL

Suggestion 1

Suggestion 2

Mysql – Finding violations of the symmetry constraint

Related Question