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?


  • The latest according to sn? And which version of MySQL? Commented Feb 23, 2018 at 20:30
  • yes according to sn
    – lost111in
    Commented Feb 23, 2018 at 20:31
  • Is sn unique or the primary key? Commented Feb 23, 2018 at 20:31
  • 1
    you can use RANK() here Commented Feb 23, 2018 at 20:33
  • 1
    @lost111in yes, it'll all be faster and more sane if you migrate to PostgreSQL. =) Commented Feb 23, 2018 at 20:45

2 Answers 2


With MySQL 8

SELECT sn, color, value
  FROM table
) AS t
WHERE t.r <= 2;

Using MySQL < 8

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

  • using MySQL 5.7 on Ubuntu 16.04 so getting error
    – lost111in
    Commented Feb 23, 2018 at 20:53

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.*
    ( 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.

  • The MySQL solution to this problem typically uses their special variables to create rank. Commented Feb 23, 2018 at 20:43
  • @EvanCarroll yeah, I variables can be used, too. I prefer this method. There's also a method that (ab)uses GROUP_CONCAT(). Commented Feb 23, 2018 at 20:45
  • Works, however taking 15 seconds on average
    – lost111in
    Commented Feb 23, 2018 at 20:58
  • @lost111in share the output of SHOW CREATE TABLE tblname; if you like. Do you have or did you add an index on (color, sn)? What's the size of the table (number of rows)? How many distinct colors? Commented Feb 23, 2018 at 21:37
  • @ypercubeᵀᴹ Thanks, I have separate index on color and sn, around 20k rows and 10 colors, however ended by using dba.stackexchange.com/questions/198712
    – lost111in
    Commented Feb 24, 2018 at 1:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.