DISTINCT ON for unique per group in PostgreSQL - Step-by-Step Execution

Concept Flow - DISTINCT ON for unique per group

Start with table data

↓

Sort rows by group and criteria

↓

Scan rows top to bottom

↓

For each group, pick first row only

↓

Return unique rows per group

DISTINCT ON picks the first row for each group after sorting, giving one unique row per group.

Execution Sample

PostgreSQL

SELECT DISTINCT ON (category) category, price
FROM products
ORDER BY category, price DESC;

Selects one product per category with the highest price.

Execution Table

Step	Row Read	category	price	Action	Output Rows
1	1	Books	20	First Books row, keep	[Books, 20]
2	2	Books	15	Same category Books, skip	[Books, 20]
3	3	Electronics	100	First Electronics row, keep	[Books, 20], [Electronics, 100]
4	4	Electronics	80	Same category Electronics, skip	[Books, 20], [Electronics, 100]
5	5	Toys	25	First Toys row, keep	[Books, 20], [Electronics, 100], [Toys, 25]
6	6	Toys	10	Same category Toys, skip	[Books, 20], [Electronics, 100], [Toys, 25]
7	End	-	-	No more rows	[Books, 20], [Electronics, 100], [Toys, 25]

💡 All rows scanned; only first row per category kept due to DISTINCT ON.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	Final
Output Rows	[]	[Books, 20]	[Books, 20]	[Books, 20, Electronics, 100]	[Books, 20, Electronics, 100]	[Books, 20, Electronics, 100, Toys, 25]	[Books, 20, Electronics, 100, Toys, 25]	[Books, 20, Electronics, 100, Toys, 25]

Key Moments - 2 Insights

Why do some rows with the same category get skipped?

DISTINCT ON keeps only the first row per category after sorting. Later rows with the same category are skipped, as shown in steps 2, 4, and 6 in the execution table.

Why is ORDER BY important with DISTINCT ON?

ORDER BY decides which row is first per group. Without correct sorting, DISTINCT ON might pick unexpected rows. Here, ordering by category then price DESC ensures highest price per category is chosen.

Visual Quiz - 3 Questions

Look at the execution table, what is the output after step 3?

A [Books, 20]

B [Books, 20], [Electronics, 80]

C [Books, 20], [Electronics, 100]

D [Electronics, 100]

💡 Hint

Check the 'Output Rows' column at step 3 in the execution table.

At which step does the condition to skip a row with category 'Toys' occur?

A Step 5

B Step 6

C Step 4

D Step 2

💡 Hint

Look for rows where action says 'skip' and category is 'Toys' in the execution table.

If ORDER BY changed to price ASC, which row would DISTINCT ON pick first for category 'Books'?

A Books with price 15

B Books with price 20

C Books with price 10

D Books with price 25

💡 Hint

DISTINCT ON picks the first row after sorting; changing ORDER BY to price ASC sorts lowest price first.

Concept Snapshot

DISTINCT ON (expression) selects first row per group.
ORDER BY must include the DISTINCT ON expression.
Rows are sorted first; then first row per group is kept.
Useful to get unique groups with preferred row.
Only in PostgreSQL, not standard SQL.

Full Transcript

DISTINCT ON in PostgreSQL helps select one unique row per group. It works by sorting the table rows by the group column(s) and other criteria, then scanning top to bottom. For each group, it keeps only the first row it sees and skips the rest. This way, you get one row per group, for example, the highest priced product in each category. The ORDER BY clause is very important because it controls which row is first per group. The execution table shows step-by-step how rows are read and either kept or skipped. Beginners often wonder why some rows are skipped or why ORDER BY matters; the execution steps clarify these points. This feature is handy for queries needing unique groups with a preferred row, but remember it is specific to PostgreSQL.