Re: ANSI SQL proposal: SELECT DISTINCT ON (... ORDER BY ...) and UNION DISTINCT ON (... ORDER BY ...)

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