view raw
Y Kal Y Kal - 8 months ago 55
MySQL Question

MySQL - Select 3 highest values and sort alphabetically

I have this table with thousands of rows:

id | Keyword | hits
1 cat 3
2 cats 5
3 castle 1
4 cream 2
5 car 12
. . .

I want to select the 3 rows with most hits and then order alphabetically.
So I want to return:


I have this:

SELECT keyword,hits FROM table ORDER BY hits DESC, keyword ASC LIMIT 3

but it only orders by hits and then if hits are equal it orders alphabetically.


Wrap your SELECT - that gets only the 3 rows with highest hits - as a subquery inside another query that orders by the keyword:

SELECT *                    --- or just: SELECT keyword
  ( SELECT keyword, hits 
    FROM table 
    ORDER BY hits DESC
           , keyword ASC    --- this is needed only in case of ties at 3rd place
    LIMIT 3
  ) tmp
ORDER BY keyword