For each row in table line there can be many rows in table line_version. I need to find only N (let's say 2) "latest" rows for each line row. Latest here means the largest IDs.
For example, that's how line_version table rows look like (line_id references the line table):
id | line_id
-------+---------
10 | 1
20 | 1
30 | 1
40 | 1
50 | 2
60 | 2
70 | 2
Looking for the 2 latest line_version row IDs per line row, it should be (30, 40, 60, 70).
Globally, I can get 2 latest ones by simply doing:
SELECT id from line_version ORDER BY id DESC LIMIT 2
But how can I get the latest 2 per line_id?
YOu can use the below with condition rownumber>=count1-(N-1).The sql fiddle here.For last row you can use rownumber>=count1.last two rows rownumber>=count1-1,three rows rownumber>=count1-2
with data as (select
10 id , 1 line_id union all
select 20 , 1 union all
select 30 , 1 union all
select 40 , 1 union all
select 50 , 2 union all
select 60 , 2 union all
select 70 , 2),
subset as (select a.*,ROW_NUMBER() over(partition by line_id order by id ) rownumber,count(id) over(partition by line_id) count1 from data a)
select id,line_id from subset where rownumber>=count1-1;
To improve the performance you can do away with the count1 with the below using rownumber<=N.
with data as (select
10 id , 1 line_id union all
select 20 , 1 union all
select 30 , 1 union all
select 40 , 1 union all
select 50 , 2 union all
select 60 , 2 union all
select 70 , 2),
subset as (select a.*,ROW_NUMBER()
over(partition by line_id order by id desc)
rownumber from data a)
select id,line_id from subset
where rownumber<=2 order by line_id,id;