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
