Fetch first 9 records and make one more record which will be as "Others" in MySQL I am fetching records from one table with count of one field with other field as name.
I want only 10 records. out of which 9 records give me field and its count. but i want to show 10 record as "Others" with all remaining fields with count. This is something like wrapping records.
Something like below is table contents.

emp_id | designation

 1   |   software Engg.

 2   |   software Engg.

 3   |   Project Manager


not less than 10 designation.

And I want to show first 10 records as
Software Engineers  20
Project Manager     5
....
....
....
Others    50


Is there any way to make SQL Query for mysql db so that it will be fast and save time in application level where I am adding up record counts for "Others" ?

Please suggest how I can make it possible in effective way.
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&lt;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&lt;10,row_number,10);