Using DISTINCT ON in PostgreSQL

When using DISTINCT ON in PostgreSQL, the columns specified in the DISTINCT ON clause must appear first in the ORDER BY clause and in the same order. This is a requirement to ensure PostgreSQL knows how to determine the "first" row for each distinct group.

For example:

SELECT DISTINCT ON (user_id, event_name) id, user_id, event_name, inquiry_id, created_at
FROM public.persona_inquiry
ORDER BY user_id, event_name, created_at DESC;

Key points:
• DISTINCT ON (user_id, event_name) selects the first row for each unique (user_id, event_name) combination.
• ORDER BY user_id, event_name ensures that the sorting starts with the same columns as the DISTINCT ON clause.
• Additional columns in ORDER BY (like created_at DESC) determine which row to pick when there are duplicates.
Mistakenly not matching the DISTINCT ON columns with the start of the ORDER BY clause will result in an error:
SELECT DISTINCT ON expressions must match initial ORDER BY expressions.

#postgres #sql #database