DISTINCT ON (PostgreSQL)
intermediateadvanced
2 min read
What is DISTINCT ON?
DISTINCT ON (columns) is a PostgreSQL extension that returns only the first row for each unique combination of the specified columns. The "first" row is determined by the ORDER BY clause.
Syntax
SELECT DISTINCT ON (group_column)
group_column, other_columns
FROM table_name
ORDER BY group_column, sort_column;When to Use
- Getting the latest record per category
- Finding the first order per customer
- Top-1-per-group queries (simpler than ROW_NUMBER)
Key Points
- PostgreSQL Only — DISTINCT ON is not standard SQL.
- ORDER BY Required — The DISTINCT ON columns must be the leftmost in ORDER BY.
- First Row Wins — For each group, the first row according to ORDER BY is returned.
- Alternative — Equivalent to ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) = 1.
- Multiple Columns — You can use DISTINCT ON (col1, col2) for composite groups.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
DISTINCT ON (PostgreSQL) Challenge
Write a query that solve this task: get the most recent enrollment for each student.
Expected result
One row per student showing their most recent enrollment.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is DISTINCT ON?
Initializing database...Each run starts from fresh sample data.
More Examples
Cheapest product per category
Get the least expensive product in each category.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Is DISTINCT ON standard SQL?
No. It is a PostgreSQL-specific extension. For portability, use ROW_NUMBER() with a subquery.
Can I use DISTINCT ON without ORDER BY?
Technically yes, but the result is unpredictable since PostgreSQL chooses an arbitrary row per group. Always pair DISTINCT ON with ORDER BY.