How to fetch only last record by created_at column for each group in SQL-CodePudding

I have a table events with columns:

create table event
(
    id             bigserial
        primary key,
    created_at     timestamp with time zone default now() not null,
    type           text                                   not null,
    package_id     bigint                                 not null,
    data           jsonb
);

I would like to fetch rows by a list of package_ids but only the last (latest creation date) row by column created_at for each package_id. So, for example if I have:

   id | created_at                       | type                 | package_id
    1   2022-02-02 14:46:17.906101  00:00  ACCEPTED_TERMINAL_DPT  215
    2   2022-02-02 14:50:23.740839  00:00  LOADED_ON_FLIGHT       215
    3   2022-02-02 14:50:49.132368  00:00  STORAGE_IN_TERMINAL    215
    4   2022-02-02 14:50:57.067934  00:00  HANDED_OVER_TO_DRIVER  215
    5   2022-02-02 16:20:51.306371  00:00  CUSTOMS_EXPORT         215
    6   2022-02-02 16:21:45.661580  00:00  HELD_IN_CUSTOMS        215
    7   2022-02-02 16:36:20.434390  00:00  CUSTOMS_IMPORT         215
    10  2022-02-03 14:12:39.751217  00:00  CUSTOMS_EXPORT         241
    11  2022-02-03 14:12:42.979952  00:00  HANDED_OVER_TO_DRIVER  241

From this rows I would like to fetch only 2 rows (these are the rows with the latest creation date for each package id):

7   2022-02-02 16:36:20.434390  00:00  CUSTOMS_IMPORT         215
11  2022-02-03 14:12:42.979952  00:00  HANDED_OVER_TO_DRIVER  241

Something like this obviously won't work:

select *
from event
where package_id in (215, 241)
group by created_at

So, I wonder what kind of query could give me such a result?

CodePudding user response：

This should work for you?

with events as (
  select 
   *,
   row_number() over (partition by package_id order by created_at desc) as row_number
  from event
  where package_id in (215, 241)
)
select * from events where row_number = 1

CodePudding user response：

Sort by package_id and descending created_at, apply Distinct on in the select.

select distinct on (package_id) *
  from event
where package_id in (215, 241)
order by package_id, created_at desc;

Sorting descending created_at works because:

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions ... equal