Latest order per customer: the SQL that gets it right
Short answer: To get one row per group, such as each customer's latest order, use DISTINCT ON in PostgreSQL with an ORDER BY that puts the row you want first, or use the row_number() window function and keep rows numbered 1. For "top N per group", such as the three best-selling products in each category, row_number() in a subquery is the standard pattern. Always include a tie-breaker in the ordering, and add an index that matches it so the query stays fast as data grows.
The problem in plain terms
Business apps constantly need "the latest X for each Y":
- each customer's most recent order, for a CRM list
- the current price for each product from a price history table
- the last status change for each support ticket
- the top three products by revenue in each category
The naive versions go wrong in predictable ways. Grouping by customer and taking max(created_at) gets the latest date but not the rest of that order's columns. Looping in application code, one query per customer, works on test data and crawls on real data. Some databases let you select columns that aren't grouped and return an arbitrary row, which looks correct until it isn't.
Option 1: DISTINCT ON (PostgreSQL)
PostgreSQL's SELECT documentation describes DISTINCT ON (expression) as keeping only the first row of each set of rows where the expression is equal. The important part is what "first" means: the docs warn it's unpredictable unless ORDER BY puts the row you want first, and the DISTINCT ON expressions must match the leftmost ORDER BY expressions.
Latest order per customer:
SELECT DISTINCT ON (customer_id)
customer_id, id, created_at, total_cents, status
FROM orders
ORDER BY customer_id, created_at DESC, id DESC;
customer_id comes first in the ordering because it's the DISTINCT ON expression. created_at DESC makes the newest order first within each customer. id DESC breaks ties when two orders share a timestamp.
It's concise and readable, but it's PostgreSQL-specific, and it only returns one row per group.
Option 2: window functions
PostgreSQL's window functions tutorial explains that a window function performs a calculation across rows related to the current row. PARTITION BY splits rows into groups, and ORDER BY inside OVER sets the order within each group. row_number() numbers rows within each group: 1, 2, 3 and so on.
The tutorial also notes that window functions only see rows that survive the WHERE, GROUP BY and HAVING clauses, and that to filter on a window function's result, you use a sub-select. So:
SELECT * FROM (
SELECT o.*, row_number() OVER (
PARTITION BY customer_id ORDER BY created_at DESC, id DESC
) AS rn
FROM orders o
) ranked
WHERE rn = 1;
Change rn = 1 to rn <= 3 and you have the top three per group, which DISTINCT ON can't do. This pattern also works in most other modern databases.
Top products per category
Combine aggregation and ranking:
SELECT * FROM (
SELECT category_id, product_id, sum(total_cents) AS revenue,
row_number() OVER (PARTITION BY category_id
ORDER BY sum(total_cents) DESC) AS rn
FROM order_lines
GROUP BY category_id, product_id
) t
WHERE rn <= 3;
The grouping happens first, then the window function ranks the grouped results.
Ties: row_number, rank or dense_rank?
row_number()always gives unique numbers, picking an order among ties based on yourORDER BY. Add a tie-breaker column so the result is stable.rank()gives tied rows the same number and leaves gaps.dense_rank()gives tied rows the same number without gaps.
For "latest order", use row_number() with a tie-breaker. For "top three sellers, including joint third place", rank() may be what the business means. Ask.
Keep it fast
These queries sort within each group, so an index matching the partition and order helps a lot:
CREATE INDEX ON orders (customer_id, created_at DESC, id DESC);
For a list page that shows 50 customers, filter to those customers first, in the inner query, rather than ranking every order in the database and then discarding most of them.
Put it in one place
If several screens need "latest order per customer", wrap the query in a database view, such as customer_latest_order, or a single function in your data layer. Every screen then uses the same definition of "latest", including the same tie-breaker and the same rule for cancelled orders. When the business later decides refunds shouldn't count, you change it once.
Common mistakes
- No ORDER BY with DISTINCT ON, giving a random order per customer.
- No tie-breaker, so the "latest" order flips between page loads when timestamps match.
- Filtering on rn in the same query level where it's calculated; it needs the sub-select.
- Using the pattern on huge tables without an index, then blaming the database.
- Time zones: "today's latest order" depends on which day boundary you use.
A worked example
An illustrative Norwich garden centre's customer list in its in-house CRM showed each customer's latest order. The original code ran one query per customer, and the page slowed as the customer base grew. A rewrite using GROUP BY returned the latest date but the wrong order totals. The developer replaced both with a DISTINCT ON query, added an index on customer and order date, and used row_number() for a new "top three plants per category" report. Both pages were correct and quick.
FAQ
Is DISTINCT ON standard SQL?
No, it's a PostgreSQL extension. Window functions are the portable option.
Can my ORM do this?
Many ORMs support window functions or raw SQL fragments. Check its documentation, or use a database view.
Why not just sort in the app?
For small lists it's fine. For thousands of customers, let the database do it with an index.
What about the latest record across several tables?
Combine them with UNION ALL in a subquery, then apply the same ranking.
If your app's lists show the wrong "latest" rows or slow down as data grows, send us the query and we'll help you rewrite it.