Message-ID: <f17dd31391b98c32b98ffcadb7aed164229695ba.camel@cybertec.at>
Subject: Re: How can I create a feature request for QUALIFY clause into
 PostgreSQL?
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Onni Hakala <onni@keksi.io>, pgsql-novice@lists.postgresql.org
Date: Mon, 03 Oct 2022 20:11:17 +0200
In-Reply-To: <6E722EEC-61FE-4EC0-B17D-B4468DA34BA1@keksi.io>
References: <6E722EEC-61FE-4EC0-B17D-B4468DA34BA1@keksi.io>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.42.4 (3.42.4-2.fc35) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/f17dd31391b98c32b98ffcadb7aed164229695ba.camel%40cybertec.at>
Precedence: bulk

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