Top-N per group, DISTINCT ON and gaps and islands

conceptThe concept

SELECT DISTINCT ON (customer_id) ... ORDER BY customer_id, created_at DESC keeps the first row of each customer according to the ORDER BY. The DISTINCT ON expressions must lead the ORDER BY. It is PostgreSQL-specific and very concise.

For the top N, number rows with row_number() OVER (PARTITION BY g ORDER BY x DESC) in a subquery and keep rn <= N. This sorts every row. When groups are many and an index on (g, x) exists, LATERAL (... ORDER BY x DESC LIMIT N) per group can be far faster because each probe reads only N index entries.

Gaps and islands: for consecutive values, value - row_number() is constant within a run. Group by that difference and every group is one island. The same trick works for dates: day - row_number()::int.

whyWhy it matters

"Latest status per device", "top 3 products per category", "longest login streak" are everyday requirements. Picking the right pattern is the difference between a 5 ms and a 5 s query at scale.

Use caseReal-world scenario

The home page shows the three best-rated products per category, the CRM shows each customer's latest order, and the loyalty programme rewards customers active on 3 or more consecutive days.

Try itExamples

Run and edit these in the interactive lesson.

SELECT DISTINCT ON (o.customer_id)
       o.customer_id, o.id AS order_id, o.created_at::date AS day, o.status
FROM orders o
WHERE o.customer_id <= 5
ORDER BY o.customer_id, o.created_at DESC;

SELECT category, name, avg_rating, reviews
FROM (
  SELECT p.category, p.name,
         round(avg(r.rating), 2) AS avg_rating,
         count(*) AS reviews,
         row_number() OVER (PARTITION BY p.category ORDER BY avg(r.rating) DESC, count(*) DESC, p.id) AS rn
  FROM products p
  JOIN reviews r ON r.product_id = p.id
  GROUP BY p.id
  HAVING count(*) >= 10
) ranked
WHERE rn <= 3
ORDER BY category, rn;

SELECT c.category, top.name, top.price
FROM (SELECT DISTINCT category FROM products) c
CROSS JOIN LATERAL (
  SELECT name, price FROM products p
  WHERE p.category = c.category
  ORDER BY price DESC
  LIMIT 2
) top
ORDER BY c.category, top.price DESC;

WITH days AS (
  SELECT DISTINCT customer_id, (at AT TIME ZONE 'UTC')::date AS day
  FROM events
  WHERE customer_id BETWEEN 1 AND 300
), grouped AS (
  SELECT customer_id, day,
         day - (row_number() OVER (PARTITION BY customer_id ORDER BY day))::int AS island
  FROM days
)
SELECT customer_id, min(day) AS streak_start, max(day) AS streak_end, count(*) AS days
FROM grouped
GROUP BY customer_id, island
HAVING count(*) >= 3
ORDER BY days DESC, customer_id
LIMIT 10;

Avoid / preferDo this, not that

SELECT * FROM orders o
WHERE created_at = (SELECT max(created_at) FROM orders WHERE customer_id = o.customer_id);

SELECT DISTINCT ON (customer_id) *
FROM orders
ORDER BY customer_id, created_at DESC, id DESC;

row_number() OVER (PARTITION BY category ORDER BY price)

row_number() OVER (PARTITION BY category ORDER BY price, id)

PitfallsCommon mistakes

• Writing DISTINCT ON without an ORDER BY that decides which row wins. The result is then arbitrary.
• Using rank() for "exactly N per group" when ties exist: you get more than N rows.
• Running the row_number pattern over a huge table to fetch the top 3 for a handful of groups. Filter first, or use LATERAL with a supporting index.

LimitsWhen not to use it

DISTINCT ON is PostgreSQL-specific. For SQL that must run on other databases, use row_number() in a subquery.

SecuritySecurity notes

In multi-tenant tables, filter by tenant before ranking. Ranking across tenants and filtering afterwards can leak how many rows other tenants have through rank numbers.

ChallengeTry it yourself

SELECT DISTINCT ON (category) category, name, price
FROM products
WHERE active
ORDER BY category, price, id;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Window functions tutorial
• Window function calls