Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 90 ยท Querying with PostgreSQL Features

DISTINCT ON and Top-N per Group

Learn PostgreSQL DISTINCT ON to pick one row per group, how it differs from ROW_NUMBER for top-N, and how FETCH FIRST WITH TIES keeps tied rows.

"Show me each customer's latest order." "Give me the cheapest product in every category." "Top three articles per author." These questions all have the same shape: group the rows, rank them inside each group, keep the first one (or the first few). They are among the most common queries in real applications, and GROUP BY cannot answer them, because it throws away the very row you want to see.

PostgreSQL has a purpose-built feature for the "one row per group" case, DISTINCT ON, and the standard window-function approach for the "top N per group" case. In this lesson you learn both, how to choose, and how FETCH FIRST ... WITH TIES behaves when several rows tie for last place. DISTINCT ON and WITH TIES are PostgreSQL features, so those blocks are read-only. The portable ROW_NUMBER and RANK versions run live on the bookstore sample.

What runs live. The <TryIt> blocks use sql.js (SQLite) with the bookstore dataset. DISTINCT ON and FETCH FIRST ... WITH TIES are not available there, so each is shown next to a live query that produces the same result.

What DISTINCT ON does

An ordinary SELECT DISTINCT removes rows that are identical in every selected column. DISTINCT ON (expression, ...) is different: it keeps only the first row of each set of rows that share the same values for the listed expressions. Which row counts as "first" is decided by ORDER BY.

SQL
-- PostgreSQL (read-only)
SELECT DISTINCT ON (o.customer_id)
       o.customer_id, o.ordered_on, b.title
FROM   orders AS o
JOIN   books  AS b ON b.id = o.book_id
ORDER  BY o.customer_id, o.ordered_on DESC, o.id DESC;

Read it in two steps. First PostgreSQL sorts all rows by the ORDER BY, so each customer's orders sit together with the newest first. Then, for each distinct customer_id, it keeps the first row it sees. The result is one row per customer: their most recent order, with every column of that row available. That last part is the point. Compare GROUP BY customer_id with MAX(ordered_on): you get the date, but not the title that belongs to that date, unless you add a join or subquery.

DISTINCT ON in three steps

Sort ORDER BY puts each group together.
Keep first One row per DISTINCT ON key.
Full row Every column comes from that row.
1 / 2

The ORDER BY rules

Two rules cause nearly all the confusion.

The leftmost ORDER BY expressions must match the DISTINCT ON expressions. If you write DISTINCT ON (customer_id) but ORDER BY ordered_on DESC, PostgreSQL stops with SELECT DISTINCT ON expressions must match initial ORDER BY expressions. It needs the rows grouped by customer before it can pick the first of each group. You can add more sort keys after the matching ones, and those decide which row wins.

Without a full ordering, which row wins is not defined. If two of Hana's orders were placed on the same day and you sort only by date, either one may come back, and it may change between runs or after a vacuum. Always end the ORDER BY with a unique column such as the primary key, as the example does with o.id DESC.

Two smaller facts: NULLs count as equal for grouping, so all rows with a NULL key form one group, and the ORDER BY also controls the order of the output. If you want the result sorted by something else (say, newest customers first), wrap the query in a subquery and sort the outer one.

The portable version: ROW_NUMBER

DISTINCT ON is concise but exists only in PostgreSQL. The standard approach numbers the rows inside each group with ROW_NUMBER and keeps number one. It runs everywhere, including SQLite, so you can try it here:

SQL Latest order per customer with ROW_NUMBER ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

You get five rows. Hana Novak placed two orders on 2024-02-14; the tie-breaker o.id DESC makes Paper Lanterns (order 2) win over The Salt Road (order 1), and it would do so on every run. The PARTITION BY is the DISTINCT ON key, and the ORDER BY inside OVER is the sort that picks the winner.

Top N per group

DISTINCT ON can only keep one row per group. When you want the top two or three, you need the window function, because you can keep any rank you like:

SQL The two most expensive books per genre ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Genres with only one book (Children, Poetry, Travel) return one row, while Fiction, Mystery and Science return two. Filtering on rn has to happen in an outer query, because window functions are evaluated after WHERE.

Need Use
One row per group, PostgreSQL only, shortest code DISTINCT ON
One row per group, portable ROW_NUMBER() ... WHERE rn = 1
Top N per group (N > 1) ROW_NUMBER() ... WHERE rn <= N
Keep ties for the last place RANK() or DENSE_RANK()
Per-group ranking from a join, many groups LATERAL with ORDER BY ... LIMIT N

Performance notes

DISTINCT ON needs the rows sorted by the key and then by the ordering columns. A composite index that matches the ORDER BY lets PostgreSQL read the rows in that order and skip the sort:

SQL
-- PostgreSQL (read-only)
CREATE INDEX orders_customer_latest_idx ON orders (customer_id, ordered_on DESC, id DESC);

This works well when groups are small or when you need all the groups. When there are few groups with very many rows each (say, a hundred customers and fifty million orders), the query still has to walk through a huge number of rows to pick one per group. In that case a LATERAL join with LIMIT 1 per group, which does one index probe per group, is much faster. The query planner and tools to confirm this are covered in EXPLAIN and EXPLAIN ANALYZE.

FETCH FIRST ... WITH TIES

LIMIT n cuts at exactly n rows, even when the next row has the same sort value as the last one you kept. The SQL-standard FETCH FIRST n ROWS WITH TIES (PostgreSQL 13 and later) keeps those tied rows too:

SQL
-- PostgreSQL (read-only)
SELECT name, score
FROM   contestants
ORDER  BY score DESC
FETCH FIRST 2 ROWS WITH TIES;

If the scores are 90, 85, 85, 70, the plain LIMIT 2 returns the 90 and one of the two 85s, chosen arbitrarily, which is unfair. WITH TIES returns the 90 and both 85s, so the result can have more than n rows. It requires an ORDER BY, because "tie" only has meaning relative to a sort. FETCH FIRST n ROWS ONLY is simply the standard spelling of LIMIT n.

WITH TIES is a whole-query limit, not per group. For ties per group, use RANK() inside the partition, as you can see in this live example, which compares LIMIT 2 with a rank-based version on a tie:

SQL LIMIT versus keeping the ties (RANK)

Press Run (or Ctrl + Enter) to see the result here.

The first query returns a and b (and only because of the extra name in the sort; without it the choice between b and c is arbitrary). The second returns a, b and c, which is exactly what FETCH FIRST 2 ROWS WITH TIES does on PostgreSQL.

Choosing between them

Start with DISTINCT ON when you are on PostgreSQL and want exactly one row per group: it is short, readable and index-friendly. Switch to ROW_NUMBER when you need more than one row, when you want the query to be portable, or when you need the rank itself in the output. Keep a unique tie-breaker in every ranking ORDER BY, because "the latest" is only meaningful when the order is total.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does DISTINCT ON (customer_id) keep?

    PostgreSQL sorts by the ORDER BY and keeps the first row for each distinct key. Which row is first depends entirely on the ORDER BY.
  2. 2. Why does SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY ordered_on DESC raise an error?

    PostgreSQL requires the leftmost ORDER BY expressions to match the DISTINCT ON expressions, so the rows of each group are adjacent. Here ORDER BY customer_id, ordered_on DESC would work.
  3. 3. You need the three newest articles per author. Which approach fits?

    DISTINCT ON keeps only one row per group, and FETCH FIRST limits the whole result, not each group. A window function lets you keep any number of rows per partition.
  4. 4. Scores are 90, 85, 85, 70. What does ORDER BY score DESC FETCH FIRST 2 ROWS WITH TIES return?

    WITH TIES keeps every row that ties with the last row inside the limit, so both 85s are returned. A plain LIMIT 2 would drop one of them.

Summary

  • DISTINCT ON (keys) keeps the first row of each key group, as decided by ORDER BY; every column comes from that row.
  • The ORDER BY must begin with the DISTINCT ON expressions, and should end with a unique column so the winner is stable.
  • For top-N per group, or for portable code, use ROW_NUMBER() OVER (PARTITION BY ...) and filter on the number in an outer query.
  • FETCH FIRST n ROWS WITH TIES (PostgreSQL 13+) keeps rows tied with the last one; RANK() does the same per group.
  • With few groups and many rows each, a LATERAL join with LIMIT is usually faster than either.

Next: FILTER, GROUPING SETS, CUBE and ROLLUP.