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
