capture first n row in an group.

capture_n_row_in_a_group.sql

/*
https://dbfiddle.uk/DRmCu_7u
*/

begin;
create table observations(
    id bigint primary key generated always as identity
    ,station_id int
    ,create_at timestamptz
    ,foo text
);

insert into observations(station_id,create_at, foo)
select ceil(random() * 10)::int
    ,'2022-01-01'::timestamptz + interval '1s' * g
    ,'asjldkhk'||g
from generate_series(1,100000) g;

create table station(station_id int primary key);
insert into station select generate_series(1,10);
commit;

------------------------------------------------
--2 observations per station.
select s.station_id,o.create_at, o.id, o.foo
from station s
cross join lateral(
    select o.id, o.create_at, o.foo from observations o
    where o.station_id = s.station_id
    order by o.create_at desc
    FETCH FIRST 1 ROWS WITH TIES
) o
order by s.station_id, o.create_at;


--------------------------
-- first one rows only capture.
-- Without table stations:
--capture latest rows. 
--2 
with recursive cte as(
(select station_id from observations 
    order by station_id limit 1
)
union all
select l.station_id from cte c
cross join lateral
    (select o.station_id from observations o
        where o.station_id > c.station_id 
        order by station_id limit 1
    ) l
)
select c.station_id,o.create_at ,o.id
from cte c
cross join lateral(
    select o.id, o.create_at
    from observations o where o.station_id = c.station_id
    order by create_at desc
    limit 1
) o
where c.station_id is not null;


---------capture the second row in an group. 

with recursive cte as(
(select station_id from observations 
    order by station_id limit 1
)
union all
select l.station_id from cte c
cross join lateral
    (select o.station_id from observations o
        where o.station_id > c.station_id 
        order by station_id limit 1
    ) l
)
select c.station_id,o.create_at ,o.id
from cte c
cross join lateral(
    select o.id, o.create_at
    from observations o 
    where o.station_id = c.station_id
    order by create_at desc
    offset 1 row
    fetch first 1 row only
) o
where c.station_id is not null;