From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Onni Hakala <onni(at)keksi(dot)io> |
Cc: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, pgsql-novice(at)lists(dot)postgresql(dot)org |
Subject: | Re: How can I create a feature request for QUALIFY clause into PostgreSQL? |
Date: | 2022-10-03 20:57:40 |
Message-ID: | 1701225.1664830660@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Onni Hakala <onni(at)keksi(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | onni | 2022-10-04 04:52:03 | Re: How can I create a feature request for QUALIFY clause into PostgreSQL? |
Previous Message | Onni Hakala | 2022-10-03 19:58:52 | Re: How can I create a feature request for QUALIFY clause into PostgreSQL? |