Mysql – How to get latest 2 records of each group

greatest-n-per-groupMySQLrank

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

Best Answer

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

Related Solutions

mysql,greatest-n-per-group – How to Get Unique Records from Last 6 Months in MySQL

This type of queries are sometimes called "greatest-n-pre-group" (there's even a tag for these questions at SO!) and there are various ways to write them.

Assuming that the (customer_id, last_updated_on) is unique - i.e. there are no two rows with same last_update_on timestamp for the same customer - then you could first GROUP BY customer_id and get the maximum timestamp in a derived table and then join to the original table. Like this:
-- query 1

SELECT s.* 
FROM 
    ( SELECT customer_id,
             MAX(last_update_on) AS last_update_on
      FROM some_table
      WHERE redeemption_date > CURDATE() - INTERVAL 6 MONTH 
        AND wallet_status <> 'EXPIRED'
      GROUP BY customer_id
    ) AS m
  JOIN
    some_table AS s
      ON  s.customer_id = m.customer_id
      AND s.last_update_on = m.last_update_on 
WHERE s.redeemption_date > CURDATE() - INTERVAL 6 MONTH 
  AND s.wallet_status <> 'EXPIRED';

Another variation is to first find the customer ids (similarly to above) but then join to the table using a correlated subquery. This is often more efficient if one can find easily the distinct customer ids (so, perhaps not in your case). Here is how it can be done, so you can test both:
-- query 2

SELECT s.* 
FROM 
    ( SELECT customer_id
      FROM some_table
      GROUP BY customer_id
    ) AS d
  JOIN
    some_table AS s
      ON  s.wallet_id =                   -- the PRIMARY KEY 
          ( SELECT si.wallet_id           -- of the table
            FROM some_table AS si         
            WHERE si.customer_id = d.customer_id
              AND si.redeemption_date > CURDATE() - INTERVAL 6 MONTH 
              AND si.wallet_status <> 'EXPIRED'
            ORDER BY si.last_update_on DESC
              LIMIT 1
          );

MySQL 5.6.27: How to get latest pairs for different points in time in a single query

SET @date:='2018-01-03';

select * from ( select ta.*,
                       if(@typex=ta.group_id, 
                          @rownum:=@rownum+1, 
                          @rownum:=1+least(0,@typex:=ta.group_id)) rown 
                from stats_kpi ta, (select @rownum:=1, @typex:='_') zz
                where ts_week_start <= timestamp(@date)
                order by group_id, ts_week_start DESC
              ) yy
where rown < 3

fiddle