mysql select top n max values

Solution 1:

For n=2 you could

SELECT max(column1) m 
FROM table t
GROUP BY column2
UNION
SELECT max(column1) m
FROM table t
WHERE column1 NOT IN (SELECT max(column1) 
                      WHERE column2 = t.column2)

for any n you could use approaches described here to simulate rank over partition.

EDIT: Actually this article will give you exactly what you need.

Basically it is something like this

SELECT t.*
FROM
   (SELECT grouper,
          (SELECT val 
           FROM table li
           WHERE li.grouper = dlo.grouper
           ORDER BY
                 li.grouper, li.val DESC
           LIMIT 2,1) AS mid
   FROM 
      (
      SELECT DISTINCT grouper
      FROM table
      ) dlo 
   ) lo, table t
WHERE t.grouper = lo.grouper
      AND t.val > lo.mid

Replace grouper with the name of the column you want to group by and val with the name of the column that hold the values.

To work out how exactly it functions go step-by-step from the most inner query and run them.

Also, there is a slight simplification - the subquery that finds the mid can return NULL if certain category does not have enough values so there should be COALESCE of that to some constant that would make sense in the comparison (in your case it would be MIN of domain of the val, in article it is MAX).

EDIT2: I forgot to mention that it is the LIMIT 2,1 that determines the n (LIMIT n,1).

Solution 2:

If you are using mySQl, why don't you use the LIMIT functionality? Sort the records in descending order and limit the top n i.e. :

SELECT yourColumnName FROM yourTableName 
ORDER BY Id desc 
LIMIT 0,3