top_row_per_group postgresql

top_row_per_group.sql

/*
Find latest entries for a person_id by submission_date for specified 
filter criteria type, plan, status. 
There could be more such filters, 
but the logic to return latest by submission date is the same regardless. 
Two major uses one for paginated viewing in UI 
and second for generating reports.
https://stackoverflow.com/questions/61160156/get-paginated-rows-and-total-count-in-single-query/61160817#61160817
*/

create table my_table(
    id bigint  GENERATED BY default AS IDENTITY primary key not null
    ,h_plan_id bigint not null
    ,h_plan_submitter_id bigint
    ,last_updated timestamptz
    ,date_created timestamptz
    ,modified_by text
    ,segment_number bigint
    ,submission_date date
    ,person_id bigint
    ,status text
    ,file_id bigint
 );

insert into my_table (person_id
        ,h_plan_id
        ,last_updated
        ,submission_date
        ,status
        )
select 
    mod(i,2000) 
    ,mod((i + 400),4000) 
    ,(select '2022-09-26'::date + '1 day 3001.01s'::interval * (  mod(i,31) * random())::int)
    ,(select ('2022-07-26'::date + '1 day'::interval *  (mod(i,31) * random())::int)::date)
    ,(select s from (values ('not ok'),('ACCEPTED'), ('CORRECTED'),('not ok')) c(s) offset (i%3)::int limit 1)
from generate_series(1,1000000) g(i); 

CREATE INDEX ON my_table (submission_date DESC NULLS LAST, last_updated DESC NULLS LAST, id DESC NULLS LAST)
WHERE  status IN ('ACCEPTED', 'CORRECTED'); -- optional
CREATE INDEX ON my_table (person_id, submission_date DESC NULLS LAST, last_updated DESC NULLS LAST, id DESC NULLS LAST);

--1 with composite data type.
WITH RECURSIVE cte AS (
   (
   SELECT t  -- whole row
   FROM   my_table t
   WHERE  status IN ('ACCEPTED', 'CORRECTED')
   AND    NOT EXISTS (SELECT FROM my_table
                      WHERE  person_id = t.person_id 
                      AND   (  submission_date,   last_updated,   id)
                          > (t.submission_date, t.last_updated, t.id)  -- row-wise comparison
                      )
   ORDER  BY submission_date DESC NULLS LAST, last_updated DESC NULLS LAST, id DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT (SELECT t1  -- whole row
           FROM   my_table t1
           WHERE ( t1.submission_date, t1.last_updated, t1.id)
               < ((c.t).submission_date,(c.t).last_updated,(c.t).id)  -- row-wise comparison
           AND    t1.status IN ('ACCEPTED', 'CORRECTED')
           AND    NOT EXISTS (SELECT FROM my_table
                              WHERE  person_id = t1.person_id 
                              AND   (   submission_date,    last_updated,    id)
                                  > (t1.submission_date, t1.last_updated, t1.id)  -- row-wise comparison
                              )
           ORDER  BY submission_date DESC NULLS LAST, last_updated DESC NULLS LAST, id DESC NULLS LAST
           LIMIT  1)
   FROM   cte c
   WHERE  (t).id IS NOT NULL
   )
SELECT (t).*
FROM   cte 
limit 10;

--2 cross join lateral.
with recursive cte as(
   (SELECT t.*  
   FROM   my_table t
   WHERE  status IN ('ACCEPTED', 'CORRECTED')
   AND  NOT EXISTS (SELECT FROM my_table
                      WHERE  person_id = t.person_id 
                      AND   (  submission_date,   last_updated,id)
                          > (t.submission_date, t.last_updated, t.id)
                      )
   ORDER  BY submission_date DESC NULLS LAST, last_updated DESC NULLS LAST, id DESC NULLS LAST
   LIMIT  1
    )
union all
    select l.* from cte c 
    cross join lateral (
        select * FROM   my_table mt
        WHERE (mt.submission_date, mt.last_updated, mt.id)
               < (c.submission_date,c.last_updated,c.id) 
        AND  status IN ('ACCEPTED', 'CORRECTED')
        AND    NOT EXISTS (SELECT FROM my_table
                    WHERE  person_id = mt.person_id 
                    AND   (submission_date,   last_updated,   id)
                        > (mt.submission_date, mt.last_updated, mt.id)  -- row-wise comparison
                    )
        ORDER  BY submission_date DESC NULLS LAST, last_updated DESC NULLS LAST, id DESC NULLS LAST
        limit 1
    )l
)
table cte limit 10;

--3 with window function.
with cte as(
    select * from (
        select *
            ,row_number()
            over(partition by person_id 
                order by submission_date desc nulls last,last_updated desc nulls last, id desc)
                as rn
        from my_table) sub
    where rn = 1
    and status in ('ACCEPTED','CORRECTED')
)
select *, count(*) over() as total_row_in_cte
from cte limit 10
offset 0;

--4. with distinct on, right join on true.
with cte as (
    select distinct on (person_id) *
    from my_table
    where status in ('ACCEPTED','CORRECTED')
    order by person_id
        ,submission_date desc nulls last
        ,last_updated desc nulls last
        ,id desc
)
select * from(
    select * from cte order by person_id
    limit 10 offset 0
) sub
right join (select count(*) from cte) c(total_rows_in_cte)
on true;