MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.27


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')
;
-- requires mysql 8.0.2 or later
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;


id
value
time
id1


3770
value5
2014-11-15 22:21:55
2

3769
value4
2014-11-15 22:21:50
2

3870
value7
2014-11-16 02:01:49
1

3638
value1
2014-11-16 02:01:48
1


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;


id
value
time
id1


3770
value5
2014-11-15 22:21:55
2

3769
value4
2014-11-15 22:21:50
2

3870
value7
2014-11-16 02:01:49
1

3638
value1
2014-11-16 02:01:48
1

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