1

I need to select data grouped by external_id and resolution and ordered by timestamp but limited to first two ids in each group. I don't know how to do the latter.

I tried to do something with simple query: SELECT external_id, resolution, string_agg(id::text, ',') FROM some_table GROUP BY external_id, resolution ORDER BY timestamp LIMIT 2 but it's not enough - the limit is applicable to whole query.

Source

id external_id resolution timestamp
1 1 1D 1645941482
2 1 1D 1645941481
3 1 1D 1645941484
4 2 1D 1645941483
5 2 1D 1645941463
6 3 1D 1645941183
7 3 1D 1645941483
8 3 1D 1646941483
8 3 1D 1645741488
10 3 1D 1645941490
11 1 3D 1645941494
12 1 3D 1645941491
13 2 3D 1645941496
14 2 3D 1645941490
15 2 3D 1645941493
16 2 3D 1645941491
17 3 3D 1645941492

Expected result

external_id resolution ids
1 1D 1,2
1 3D 11,12
2 1D 4,5
2 3D 13,14
3 1D 6,7
3 3D 17
Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
lama
  • 21
  • 4

1 Answers1

2

You can use row_number() with cte to select N number of rows from any group then you can use string_agg() on that result set.

Schema and insert statements:

 create table source(id int, external_id int, resolution varchar(10), timestamp2 timestamp);


 insert into source values(1,   1   ,'1D',  to_timestamp(1645941482));
 insert into source values(2,   1   ,'1D',  to_timestamp(1645941481));
 insert into source values(3,   1   ,'1D',  to_timestamp(1645941484));
 insert into source values(4,   2   ,'1D',  to_timestamp(1645941483));
 insert into source values(5,   2   ,'1D',  to_timestamp(1645941463));
 insert into source values(6,   3   ,'1D',  to_timestamp(1645941183));
 insert into source values(7,   3   ,'1D',  to_timestamp(1645941483));
 insert into source values(8,   3   ,'1D',  to_timestamp(1646941483));
 insert into source values(8,   3   ,'1D',  to_timestamp(1645741488));
 insert into source values(10,  3   ,'1D',  to_timestamp(1645941490));
 insert into source values(11,  1   ,'3D',  to_timestamp(1645941494));
 insert into source values(12,  1   ,'3D',  to_timestamp(1645941491));
 insert into source values(13,  2   ,'3D',  to_timestamp(1645941496));
 insert into source values(14,  2   ,'3D',  to_timestamp(1645941490));
 insert into source values(15,  2   ,'3D',  to_timestamp(1645941493));
 insert into source values(16,  2   ,'3D',  to_timestamp(1645941491));
 insert into source values(17,  3   ,'3D',  to_timestamp(1645941492));

Query:

 with cte as
 (
   select id,external_id, resolution, row_number()over(partition by external_id,resolution order by timestamp2)rn from source
 )
 SELECT external_id, resolution, string_agg(id::text, ',') ids
 FROM cte
 where rn<=2
 GROUP BY external_id, resolution

Output:

external_id resolution ids
1 1D 2,1
1 3D 12,11
2 1D 5,4
2 3D 14,16
3 1D 8,6
3 3D 17

db<>fiddle here

Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26