Select two rows per key with the latest time

Question

I have data in a MySQL table tbl_test:

create table `tbl_test` (
  `id` INT(10) NOT NULL UNIQUE,
  `value` char(30),
  `time` timestamp,
  `id1` int(10)
  );

INSERT INTO
`tbl_test` (`id`,`value`,`time`,`id1`)
VALUES
  ('3638','value1','2014-11-16 02:01:48','1'),
  ('3639','value2','2014-11-14 13:00:45','1'),
  ('3642','value3','2014-11-14 13:00:40','1'),
  ('3769','value4','2014-11-15 22:21:50','2'),
  ('3770','value5','2014-11-15 22:21:55','2'),
  ('3789','value6','2014-11-14 16:08:20','2'),
  ('3870','value7','2014-11-16 02:01:49','1');

Desired result (2 rows per id1, like order by time desc limit 2 for each of them):

+------+--------+---------------------+----+
| id   | value  | time                |id1 |
+------+--------+---------------------+----+
| 3769 | value4 | 2014-11-15 22:21:50 | 2  |
| 3770 | value5 | 2014-11-15 22:21:55 | 2  |
| 3638 | value1 | 2014-11-16 02:01:48 | 1  |
| 3870 | value7 | 2014-11-16 02:01:49 | 1  |
+------+--------+---------------------+----+

The closest I got with my query:

select * from (
   select max(id) as id from tbl_test group by id1
   union all
   select max(id) from tbl_test tmp1
   where id not in (SELECT max(id) from tbl_test tmp2 where tmp1.id1 = tmp2.id1)
   group by id1
   ) as tmp
left join tbl_test USING(id);

+------+--------+---------------------+----+
| id   | value  | time                |id1 |
+------+--------+---------------------+----+
| 3770 | value5 | 2014-11-15 22:21:55 | 2  |
| 3789 | value6 | 2014-11-14 16:08:20 | 2  |
| 3642 | value3 | 2014-11-14 13:00:40 | 1  |
| 3870 | value7 | 2014-11-16 02:01:49 | 1  |
+------+--------+---------------------+----+

But I have a problem with max(id). Table are meshed and I need time desc limit 2.

http://sqlfiddle.com/#!2/f6f53/3

Answer 1

I think this is what you are looking for:

"For each id1 return the two rows with the latest time (and biggest id as tiebreaker)":

Simple with a standard SQL window function.
(MySQL finally added window functions with version 8.0.2):

SELECT id, value, time, id1
FROM  (
   SELECT t.*
        , row_number() OVER (PARTITION BY id1 ORDER BY time DESC, id DESC) AS rn
   FROM   tbl_test t   
   ) sub
WHERE  rn < 3
ORDER  BY id1 DESC, time DESC, id DESC;

In older versions substitute with with user-defined variables (MySQL-specific):

SELECT id, value, time, id1
FROM  (
   SELECT t.*
        , @rn := CASE WHEN @prev = id1 THEN @rn + 1 ELSE 1 END rn
        , @prev := id1
   FROM   tbl_test t
       , (SELECT @rn := 0) r
   ORDER BY id1 DESC, time DESC, id DESC
   ) sub
WHERE  rn < 3
ORDER  BY id1 DESC, time DESC, id DESC;

db<>fiddle here
_Oldsqlfiddle

Related question:

• In SQL, how to select the top 2 rows for each group

Answer 2

It seems that you want the rows with the most recent times for each id1.

select t.*
from tbl_test t  join
     (select id1, max(time) as maxtime
      from tbl_test
      group by id1
     ) tt
     on t.id1 = tt.id1 and t.time = tt.maxtime
order by time;

http://sqlfiddle.com/#!2/4e010/1/0