Onni Hakala <onni@keksi.io> writes:
> Can you show me how you could use that to get the latest 2 events from the events table using DISTINCT ON?
I assume you mean latest 2 events per person, else it's trivially
solved with ORDER BY ... LIMIT 2. But I'd still be inclined to
solve it with ORDER BY ... LIMIT:
=> select e.* from
(select distinct person from events) p
cross join lateral
(select e.* from events e where p.person = e.person
order by created_at desc limit 2) e;
person | event_type | created_at
--------------+-------------------+----------------------------
laurenz.albe | non-helpful reply | 2022-10-03 17:16:39.957743
someone.else | other reply | 2022-10-03 17:36:39.957743
someone.else | other reply | 2022-10-03 17:26:39.957743
onni.hakala | other reply | 2022-10-03 17:31:39.957743
onni.hakala | clarifying reply | 2022-10-03 17:21:39.957743
(5 rows)
In a real application you could probably avoid the SELECT DISTINCT
by joining to some other table that has just one row per person.
regards, tom lane