From: Vik Fearing <vik@postgresfriends.org>
To: Hannu Krosing <hannuk@google.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Cc: Peter Eisentraut <peter@eisentraut.org>
Cc: Dilip Kumar <dilipkumarb@google.com>
Subject: Re: ANSI SQL proposal: SELECT DISTINCT ON (... ORDER BY ...) and UNION DISTINCT ON (... ORDER BY ...)
Date: Thu, 17 Sep 2026 14:43:36 +0200
Message-ID: <e9b65da3-9326-4951-91d2-95f1e4c1b2a9@postgresfriends.org> (raw)
In-Reply-To: <CAMT0RQR_QqLqKdLgh3zYk7yA7oH9RJBVF+kZJ607wjJD9WKH9Q@mail.gmail.com>
References: <CAMT0RQR_QqLqKdLgh3zYk7yA7oH9RJBVF+kZJ607wjJD9WKH9Q@mail.gmail.com>
On 01/08/2026 15:08, Hannu Krosing wrote:
> Hi Vik
>
> Finally had time to put this SQL Standard Propoasl together.
>
> Please take a quick look and tell me what is missing or wrong and what
> the next steps should be.
SQL doesn't actually need DISTINCT ON. Two syntaxes already provide it:
SELECT user_id, status, updated_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at
DESC) as rn
FROM user_statuses
QUALIFY rn = 1
SELECT user_id, status, updated_at
FROM user_statuses
ORDER BY updated_at DESC
FETCH FIRST ALL PARTITIONS BY user_id, 1 ROW ONLY
However, putting the ordering inside the DISTINCT ON is a big
improvement for postgres, imo.
SELECT DISTINCT ON (user_id ORDER BY updated_at DESC)
user_id, status, updated_at
FROM user_statuses
ORDER BY status
We can't get rid of the old way of doing it, but that shouldn't prevent
us from having the new version.
I don't understand what the use case for UNION DISTINCT ON is. Could you
please provide one?
--
Vik Fearing
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: vik@postgresfriends.org, hannuk@google.com, peter@eisentraut.org, dilipkumarb@google.com
Subject: Re: ANSI SQL proposal: SELECT DISTINCT ON (... ORDER BY ...) and UNION DISTINCT ON (... ORDER BY ...)
In-Reply-To: <e9b65da3-9326-4951-91d2-95f1e4c1b2a9@postgresfriends.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox