How to get latest 2 records of each group So, I have table similar to:

sn color value
1  red   4
2  red   8
3  green 5
4  red   2
5  green 4
6  green 3


Now I need the latest 2 rows for each color, eg:

2  red   8
4  red   2
5  green 4
6  green 3


How to do it, other than using separate query for each color?

Thanks
With MySQL 8

SELECT sn, color, value
FROM (
  SELECT
    sn,
    color,
    value,
    DENSE_RANK() OVER (PARTITION BY color ORDER BY sn) AS r
  FROM table
) AS t
WHERE t.r &lt;= 2;


Using MySQL &lt; 8

You need to use their special variables.. something like this