Postgres Pro
Downloads
Home   >   mailing lists

Re: How can I create a feature request for QUALIFY clause into PostgreSQL? - Mailing list pgsql-novice

From Onni Hakala
Subject Re: How can I create a feature request for QUALIFY clause into PostgreSQL?
Date
Msg-id 02B67082-508E-4065-8AEE-0C3758021AF6@keksi.io
Whole thread Raw
In response to Re: How can I create a feature request for QUALIFY clause into PostgreSQL?  (Laurenz Albe <laurenz.albe@cybertec.at>)
Responses Re: How can I create a feature request for QUALIFY clause into PostgreSQL?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-novice
Tree view
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

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 <laurenz.albe@cybertec.at> 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

pgsql-novice by date:

Previous
From: Laurenz Albe
Date:
Subject: Re: How can I create a feature request for QUALIFY clause into PostgreSQL?
Next
From: Tom Lane
Date:
Subject: Re: How can I create a feature request for QUALIFY clause into PostgreSQL?