MySQL: Select top n max values?
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).
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
Starting from MySQL 8.0/MariaDB
support window functions which are designed for this kind of operations:
SELECT *
FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY column2 ORDER BY column1 DESC) AS r
FROM tab) s
WHERE r <= 2
ORDER BY column2 DESC, r DESC;
DB-Fiddle.com Demo