Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
From: onni@keksi.io
Mime-Version: 1.0 (1.0)
Subject: Re: How can I create a feature request for QUALIFY clause into
 PostgreSQL?
Date: Tue, 4 Oct 2022 07:52:03 +0300
Message-Id: <95FC2266-B9FF-4977-BA27-B5C70CC691D3@keksi.io>
References: <1701225.1664830660@sss.pgh.pa.us>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>,
 pgsql-novice@lists.postgresql.org
In-Reply-To: <1701225.1664830660@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/95FC2266-B9FF-4977-BA27-B5C70CC691D3%40keksi.io>
Precedence: bulk

Thanks Tom for showing me a different way to think about this problem and ye=
s I meant the latest 2 events per person. And anyway I can just use a subque=
ry for this too as shown in my earlier emails. The ergonomics of your cross j=
oin lateral don't seem that much better the subquery.

This still doesn't answer the original question of how to ask for new featur=
es in Postgres.

Best regards,
Onni Hakala

> On 3. Oct 2022, at 23:57, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>=20
> =EF=BB=BFOnni Hakala <onni@keksi.io> writes:
>> Can you show me how you could use that to get the latest 2 events from th=
e events table using DISTINCT ON?
>=20
> 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:
>=20
> =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        =20
> --------------+-------------------+----------------------------
> 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)
>=20
> In a real application you could probably avoid the SELECT DISTINCT
> by joining to some other table that has just one row per person.
>=20
>            regards, tom lane