Message-ID: <e9b65da3-9326-4951-91d2-95f1e4c1b2a9@postgresfriends.org>
Date: Thu, 17 Sep 2026 14:43:36 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: ANSI SQL proposal: SELECT DISTINCT ON (... ORDER BY ...) and
 UNION DISTINCT ON (... ORDER BY ...)
To: Hannu Krosing <hannuk@google.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>,
 Peter Eisentraut <peter@eisentraut.org>, Dilip Kumar <dilipkumarb@google.com>
References: 
 <CAMT0RQR_QqLqKdLgh3zYk7yA7oH9RJBVF+kZJ607wjJD9WKH9Q@mail.gmail.com>
Content-Language: en-US
From: Vik Fearing <vik@postgresfriends.org>
In-Reply-To: 
 <CAMT0RQR_QqLqKdLgh3zYk7yA7oH9RJBVF+kZJ607wjJD9WKH9Q@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/e9b65da3-9326-4951-91d2-95f1e4c1b2a9%40postgresfriends.org>
Precedence: bulk


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