CODEWITHSUNDEEP
sqlpostgresql
Ludwig
Ludwig

Reputation: 1801

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

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?

Upvotes: 0

Views: 1138

Answers (2)

Belayer
Belayer

Reputation: 14886

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

Upvotes: 1

VvdL
VvdL

Reputation: 3210

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

Upvotes: 0

Related Questions