0

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

  • The latest according to sn? And which version of MySQL? – ypercubeᵀᴹ Feb 23 '18 at 20:30
  • yes according to sn – lost111in Feb 23 '18 at 20:31
  • Is sn unique or the primary key? – ypercubeᵀᴹ Feb 23 '18 at 20:31
  • sn is primary key, but in future I would like to do it through time column which may not be unique – lost111in Feb 23 '18 at 20:32
  • 1
    you can use RANK() here – Edgar Allan Bayron Feb 23 '18 at 20:33
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

  • using MySQL 5.7 on Ubuntu 16.04 so getting error – lost111in Feb 23 '18 at 20:53
1

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.

  • The MySQL solution to this problem typically uses their special variables to create rank. – Evan Carroll Feb 23 '18 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(). – ypercubeᵀᴹ Feb 23 '18 at 20:45
  • Works, however taking 15 seconds on average – lost111in Feb 23 '18 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? – ypercubeᵀᴹ Feb 23 '18 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 Feb 24 '18 at 1:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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