public inbox for [email protected]  

help / color / mirror / Atom feed

From: Onni Hakala <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: [email protected]
Subject: Re: How can I create a feature request for QUALIFY clause into PostgreSQL?
Date: Mon, 3 Oct 2022 22:58:52 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

> PostgreSQL supports DISTICT ON, which can do this even more ergonomically.
Yes using DISTINCT ON one can solve some usecases but not nearly all.

I should have selected better example here in the first place.

For example I created db-fiddle of this email conversation here: https://www.db-fiddle.com/f/g2QXCdZqoaXc9XP8mHgaJT/1 <https://www.db-fiddle.com/f/g2QXCdZqoaXc9XP8mHgaJT/1;

Can you show me how you could use that to get the latest 2 events from the events table using DISTINCT ON?

CREATE TABLE events (
    person TEXT,
    event_type TEXT ,
    created_at TIMESTAMP
);

INSERT INTO events VALUES 
  ('onni.hakala','message',now()),
  ('laurenz.albe','non-helpful reply',now() + interval '30 minutes'),
  ('onni.hakala','clarifying reply',now() + interval '35 minutes'),
  ('someone.else','other reply',now() + interval '40 minutes'),
  ('onni.hakala','other reply',now() + interval '45 minutes'),
  ('someone.else','other reply',now() + interval '50 minutes');

I can write this:

SELECT * FROM (
	SELECT
		*,
  		ROW_NUMBER() OVER (PARTITION BY person ORDER BY created_at DESC) AS row_number
	FROM events
) sub
WHERE row_number <= 2

But what I would want to write is this:

SELECT *
FROM events
QUALIFY OVER (PARTITION BY person ORDER BY created_at DESC) AS <= 2

> On 3. Oct 2022, at 21:11, Laurenz Albe <[email protected]> wrote:
> 
> On Mon, 2022-10-03 at 20:38 +0300, Onni Hakala wrote:
>> I was very happy learn about QUALIFY clause in BigQuery today.
>> 
>> I have used window functions usually with subqueries like this:
>> SELECT * FROM (
>>   SELECT
>>     *,
>>     ROW_NUMBER OVER (PARTITION BY something ORDER BY modified_date DESC) AS row_number
>>   FROM table_name
>> )
>> WHERE row_number = 1
>> 
>> 
>> With QUALIFY it's much more ergonomic and cleaner to do the same thing.
>> SELECT *
>> FROM table_name
>> QUALIFY ROW_NUMBER() OVER (PARTITION BY something ORDER BY modified_date DESC) = 1
>> 
>> 
>> QUALIFY is also better since it doesn't include extra column row_number to the result.
>> 
>> Where should I send message so that Postgres maintainers would consider adding this into the TODO page: https://wiki.postgresql.org/wiki/Todo
> 
> PostgreSQL supports DISTICT ON, which can do this even more ergonomically.
> 
> Yours,
> Laurenz Albe
> -- 
> Cybertec | https://www.cybertec-postgresql.com

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]
  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