Get N most recent records in each category. Postgres

Question

Question is regarding filtering N most recent records in each category.

create table administration_entrieschangelog
(
    id              serial                   not null
        constraint administration_entrieschangelog_pkey
            primary key,
    object_id       integer                  not null
        constraint administration_entrieschangelog_object_id_check
            check (object_id >= 0),
    access_time     timestamp with time zone not null,
    as_who          varchar(7)               not null
        constraint as_who_check
            check ((as_who)::text = ANY
                   ((ARRAY ['CREATOR'::character varying, 'SLAVE'::character varying, 'MASTER'::character varying, 'FRIEND'::character varying, 'ADMIN'::character varying, 'LEGACY'::character varying])::text[])),
    operation_type  varchar(6)               not null
        constraint operation_type_check
            check ((operation_type)::text = ANY
                   ((ARRAY ['CREATE'::character varying, 'UPDATE'::character varying, 'DELETE'::character varying])::text[])),
    category_id integer                  not null

Here I want to get N most recent ( for example 4) records by access_time in each category divided by category_id. Can’t figure out how to do so outside semi-manually using UNION. For N=1 it obvious , but for N > 1 not so. Could you please advise me how to do so in a concise and generic way.

DB-Postgres 12

Thank you!

Answer 1

This is typically done using window functions:

select id, object_id, access_time, as_who, operation_type, category_id
from (
  select *, 
         dense_rank() over (partition by category_id order by access_time desc) as rn
  from administration_entrieschangelog
) t
where rn <= 4;