From: Tom Lane <tgl@sss.pgh.pa.us>
To: Onni Hakala <onni@keksi.io>
cc: Laurenz Albe <laurenz.albe@cybertec.at>, pgsql-novice@lists.postgresql.org
Subject: Re: How can I create a feature request for QUALIFY clause into
 PostgreSQL?
In-reply-to: <02B67082-508E-4065-8AEE-0C3758021AF6@keksi.io>
References: <6E722EEC-61FE-4EC0-B17D-B4468DA34BA1@keksi.io>
 <f17dd31391b98c32b98ffcadb7aed164229695ba.camel@cybertec.at>
 <02B67082-508E-4065-8AEE-0C3758021AF6@keksi.io>
Comments: In-reply-to Onni Hakala <onni@keksi.io>
	message dated "Mon, 03 Oct 2022 22:58:52 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1701224.1664830660.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 03 Oct 2022 16:57:40 -0400
Message-ID: <1701225.1664830660@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1701225.1664830660%40sss.pgh.pa.us>
Precedence: bulk

Onni Hakala <onni@keksi.io> writes:
> Can you show me how you could use that to get the latest 2 events from t=
he 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:

=3D> select e.* from
  (select distinct person from events) p
  cross join lateral
  (select e.* from events e where p.person =3D 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