How to get latest 2 records of each group

Question

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

Answer 1

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 <= 2;

Using MySQL < 8

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

Answer 2

This is a variation on the "greatest-n-per-group" problem. It could be used with window functions or LATERAL joins (also known as CROSS/OUTER APPLY), if only MySQL had implemented them^*.

Here's one method that works. I call it "poor man's cross apply":

select t.*
from 
    ( select distinct color from tbl ) as td    -- for every colour
  left join                                     -- join to the table
    tbl as t                                    -- and get all rows
  on  t.color = td.color                        -- with that color
  and t.sn >= coalesce(                         -- and sn bigger or equal than
      ( select ti.sn                            -- the 2nd higher sn value
        from tbl as ti
        where ti.color = td.color
        order by ti.sn desc
        limit 1 offset 1                        -- 1 + 1st = 2nd
      ), -9223372036854775808    -- the smallest possible bigint value,
          ) ;                    -- to cover cases of colours 
                                 -- with a single appearance, where
                                 -- the subquery returns NULL

Tested in dbfiddle.uk. An index on (color, sn) or (color, sn, value) will be used by this query. If there are only a few distinct color values, it is quite efficient.

^*: MariaDB, a fork of MySQL has indeed implemented window functions, so a solution with a ranking function, like ROW_NUMBER(), RANK() or DENSE_RANK() would work there.