public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Onni Hakala <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: [email protected]
Subject: Re: How can I create a feature request for QUALIFY clause into PostgreSQL?
Date: Mon, 03 Oct 2022 16:57:40 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>

Onni Hakala <[email protected]> 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

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: How can I create a feature request for QUALIFY clause into PostgreSQL?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox