Thanks Tom for showing me a different way to think about this problem and yes I meant the latest 2 events per person.
Andanyway I can just use a subquery for this too as shown in my earlier emails. The ergonomics of your cross join
lateraldon't seem that much better the subquery.
This still doesn't answer the original question of how to ask for new features in Postgres.
Best regards,
Onni Hakala
> On 3. Oct 2022, at 23:57, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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