From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Onni Hakala <onni(at)keksi(dot)io>, 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 18:11:17 |
Message-ID: | f17dd31391b98c32b98ffcadb7aed164229695ba.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Onni Hakala | 2022-10-03 19:58:52 | Re: How can I create a feature request for QUALIFY clause into PostgreSQL? |
Previous Message | Onni Hakala | 2022-10-03 17:38:41 | How can I create a feature request for QUALIFY clause into PostgreSQL? |