Re: How can I create a feature request for QUALIFY clause into PostgreSQL?

> 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 <laurenz(dot)albe(at)cybertec(dot)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