Re: How can I create a feature request for QUALIFY clause into PostgreSQL? - Mailing list pgsql-novice

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