Beginner LevelPostgreSQL
How does DISTINCT ON (column) select the first row for each logical group?
❓ Interview Prompt: Explain `DISTINCT ON (column_list)`, selecting top record per group, pairing with `ORDER BY`, and eliminating window function overhead.
'DISTINCT ON (expression)' is a PostgreSQL-specific extension that keeps ONLY the first row for each group of matching expressions:
How It Works:
Pair 'DISTINCT ON (group_key)' with 'ORDER BY group_key, sort_key' to retrieve the top record per group in a single pass without subqueries or window functions!
Code Implementation Example
sql
-- Find the most recent order for every customer
SELECT DISTINCT ON (customer_id) customer_id, order_date, total_amount
FROM orders
ORDER BY customer_id, order_date DESC;💡Selecting the latest order per customer using DISTINCT ON.
🎯 Interview Key Takeaway
Use DISTINCT ON (group_key) combined with ORDER BY to retrieve the top record per group efficiently.
#DISTINCT ON#ORDER BY#Grouping#Beginner
Back to all PostgreSQL Questions→