DISTINCT ON in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this DISTINCT, ORDER BY and Result Sorting, ROW_NUMBER, RANK, DENSE_RANK
What is DISTINCT ON in SQL?
DISTINCT ON returns exactly one row per distinct value of a specified expression, and you control which row it keeps using ORDER BY. It's PostgreSQL-specific syntax for the "latest record per group" type of problem — and it solves it in one clause instead of a subquery.
The pattern comes up constantly in real analytical work. Your events table has multiple rows per user and you want each user's most recent event — with all the event data, not just the user ID. Your orders table has multiple orders per customer and you want the latest order per customer. Your prices table has historical entries and you want the current price per product. All of these share the same structure: multiple rows per key, keep the one that sorts first.
The classic use case: your orders table has multiple orders per customer, and you want each customer's most recent order — with all the order data attached.
SELECT DISTINCT ON (customer_id) customer_id, id AS order_id, ordered_at::date, total_amount FROM orders ORDER BY customer_id, ordered_at DESC
DISTINCT ON (customer_id) says: deduplicate on customer_id. ORDER BY customer_id, ordered_at DESC says: sort each customer's rows by most recent first, then keep only the first row. The result is one row per customer — their most recent order with the full order data attached.
What is the difference between DISTINCT and DISTINCT ON?
This is different from plain DISTINCT. Plain DISTINCT removes rows that are identical across all selected columns. DISTINCT ON removes duplicates based only on the expressions you list in parentheses, regardless of what the other columns contain.
Why must ORDER BY start with the DISTINCT ON columns?
The one thing that trips people up
The ORDER BY clause must begin with the same expressions you listed in DISTINCT ON, in the same order. PostgreSQL requires this — it uses the sort to group duplicate keys together before picking the first row in each group.
This works:
ORDER BY customer_id, ordered_at DESCThis raises an error:
ORDER BY ordered_at DESC -- customer_id must come firstShould you use DISTINCT ON or ROW_NUMBER?
The ROW_NUMBER equivalent
The same result can be written using ROW_NUMBER:
SELECT user_id, event_type, event_time
FROM (
SELECT
user_id,
event_type,
event_time,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC) AS rn
FROM events
) ranked
WHERE rn = 1Both return the same rows. DISTINCT ON is shorter and typically faster in PostgreSQL for this pattern. ROW_NUMBER is more portable to other databases and more explicit about its logic. For teams working in PostgreSQL, DISTINCT ON is idiomatic for "one row per group, keep the top one."
One edge case: if two rows tie on the ORDER BY columns within a group — two events with the exact same timestamp for the same user — PostgreSQL picks one but doesn't guarantee which. Add a tiebreaker to ORDER BY (like a primary key) to make the result deterministic.
When does DISTINCT ON not work?
When DISTINCT ON isn't enough
DISTINCT ON always keeps the row that sorts first in each group. If you need the second-most-recent event, or the nth row, or need to filter on which row to keep based on a condition rather than sort order, DISTINCT ON can't help. For those cases, use ROW_NUMBER with a PARTITION BY. DISTINCT ON is the shortcut for the common "keep the top one" case.
Practice DISTINCT ON in SQL
Brightlane's CRM team is building a customer overview that shows each customer's most recent purchase.
Write a query to return one row per customer with at least one order, showing that customer's ID, the ID of their most recent order, when it was placed, and the order amount. Sort the final result by customer_id ascending.
Assumptions:
- A customer's most recent order is the order with the largest
ordered_atfor thatcustomer_id. - Customers with no orders on record do not appear in the result.
- The final result is sorted by
customer_idascending.
Output:
- One row per customer with at least one order, with columns
customer_id,order_id,ordered_at, andtotal_amount. Sorted bycustomer_id.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 DISTINCT ON practice problems
Write a query to return one row per customer with at least one order, showing that customer's ID, the ID of their most recent order, when it was placed, and the order amount. Sort the final result by customer_id ascending.
Write a query to return one row per status, showing the status, the ID of the most recent order in that status, when it was placed, and the order amount. Sort the final result by status ascending.
Write a query to return one row per user with at least one session, showing the user's ID, the ID of their most recent session, when it started, and the event count. Sort the final result by user_id ascending.
Write a query to return one row per customer with at least one order, showing that customer's ID, the ID of their earliest order, when it was placed, and the order amount. Sort the final result by customer_id ascending.
Write a query to return one row per customer with at least one delivered order, showing that customer's ID, the ID of their most recent delivered order, when it was placed, and the order amount. Sort the final result by customer_id ascending.
Write a query to return one row per customer-status pair on record, showing the customer ID, status, ID of the earliest order in that pair, when it was placed, and the order amount. Sort the final result by customer_id ascending, then status ascending.
Write a query to return one row per session with at least one event, showing the session ID, ID of the most recent event in that session, the event type, and when the event occurred. Sort the final result by session_id ascending.
Write a query to return one row per customer with at least one order, showing that customer's ID, their most recent order's ID, the order date, and the order amount. Sort the final result by customer_id ascending.
Write a query to return one row per category group, showing the category ID, ID of the lowest-priced product in that group, the product name, and the price. Sort the final result by category_id ascending.
Start learning to practice all 9 DISTINCT ON problems, with instant grading and mastery tracking.
Common questions about DISTINCT ON
Can DISTINCT ON take more than one column?
Yes. List them in the brackets and you get one row per distinct combination, so deduplicating on customer and status keeps the top row for each pairing rather than one row per customer.
Which row does DISTINCT ON keep when two rows tie?
Whichever the database reaches first, and that is not guaranteed to stay the same. Add a further column to the ORDER BY, usually a key, so the choice is decided by your query rather than by the plan.
Does DISTINCT ON always need an ORDER BY?
In practice yes, because without one the row you keep from each group is arbitrary. The ORDER BY is not decoration here: it is the part that says which row survives, and it has to begin with the same columns you deduplicated on.
