Postgres Greatest N Per Group: DISTINCT ON vs ROW_NUMBER
Postgres greatest n per group queries — latest order per customer, top scores per game — solved three ways: DISTINCT ON, ROW_NUMBER(), and LATERAL, with benchmarks of clarity and speed.
The postgres greatest n per group problem is one of the most-asked SQL questions in existence: "give me the latest order *per customer*," "the top 3 scores *per game*," "the most recent login *per user*." LIMIT can't express it — LIMIT 1 gives you one row *total*, not one row *per group*. PostgreSQL offers three serious answers: DISTINCT ON (a Postgres exclusive), the portable ROW_NUMBER() window function, and LATERAL joins. This guide builds all three on the same data so you can see exactly when each wins. What you'll learn Why LIMIT and GROUP BY can't solve greatest-n-per-group DISTINCT ON : the one-query Postgres answer for "latest row per group" The mandatory ORDER BY rule — and the exact error you get for breaking it The ROW_NUMBER() window-function version that works in any database DISTINCT ON vs ROW_NUMBER() : readability, ties, and performance LATERAL joins for top-N-per-group when N is greater than 1 Step 1 — The problem LIMIT can't solve Our data: an orders table where each customer has several orders, and we want each customer's *latest* order: CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INT NOT NULL, total NUMERIC(10,2) NOT NULL, placed_at TIMESTAMPTZ NOT NULL DEFAULT now() ); INSERT INTO orders (customer_id, total, placed_at) VALUES (1, 45.00, '2026-09-20 10:00+00'), (1, 120.50, '2026-09-25 14:30+00'), (1, 78.25, '2026-09-28 09:15+00'), (2, 200.00, '2026-09-21 11:00+00'), (2, 15.99, '2026-09-29 16:45+00'), (3, 99.00, '2026-09-22 08:00+00'); The naive attempts fail in instructive ways. ORDER BY placed_at DESC LIMIT 1 returns one row total — customer 2's latest — not one per customer. GROUP BY customer_id with MAX(placed_at) gives you the latest…