Definition
- "One row per group" – solved with
ROW_NUMBER() OVER (PARTITION BY group_key ORDER BY sort_key), which numbers rows within each partition starting at 1. - Latest order –
ORDER BY created_at DESC, id DESCand filterrn = 1returns the most recent order per customer as a full row. - Most expensive order –
ORDER BY total_amount DESC, id DESCand filterrn = 1returns the biggest order per customer. - Top-3 per group – change the filter to
rn <= 3to return the top three rows per partition. - Tie-breaker –
id DESCensures deterministic results when two rows share the same sort value. - Why not GROUP BY –
GROUP BYwithMAX()returns only the aggregate value;ROW_NUMBERreturns the entire row.
National Bank of India wants, for each customer, their most recent order and their biggest order. Imagine making one stack of each customer's orders. For "latest," sort each stack newest-on-top and take the top slip. For "most expensive," sort each stack biggest-amount-on-top and take the top slip. PARTITION BY customer makes the per-customer stacks, ORDER BY decides what "top" means, ROW_NUMBER tags each slip 1, 2, 3, and keeping tag 1 gives the top row.
Understanding the Concepts
What is the one-row-per-group problem?
The one-row-per-group problem asks: for each group (each customer, each account, each branch), return a single row that is the "best" by some criterion – the latest, the most expensive, the highest-rated. It is the most common use of ROW_NUMBER in production.
-- Goal: one row per customer – their latest order
How do you get the latest order per customer?
The latest order per customer uses ROW_NUMBER partitioned by customer and ordered by date descending.
SELECT *
FROM (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY created_at DESC, order_id DESC
) AS rn
FROM orders o
) ranked
WHERE rn = 1;
customer_id | order_id | total_amount | created_at | rn
------------+----------+--------------+-------------+----
C001 | O015 | 85000.00 | 2024-03-15 | 1
C002 | O022 | 42000.00 | 2024-03-12 | 1
C003 | O018 | 120000.00 | 2024-03-14 | 1
The order_id DESC tie-breaker is important because two orders could share the same timestamp. Without it, ROW_NUMBER picks one arbitrarily and the result can change between runs.
How do you get the most expensive order per customer?
The same shape with a different sort key.
SELECT *
FROM (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY total_amount DESC, order_id DESC
) AS rn
FROM orders o
) ranked
WHERE rn = 1;
customer_id | order_id | total_amount | created_at | rn
------------+----------+--------------+-------------+----
C001 | O003 | 250000.00 | 2024-01-10 | 1
C002 | O009 | 180000.00 | 2024-02-05 | 1
C003 | O018 | 120000.00 | 2024-03-14 | 1
How do you get the top-3 per group?
Change the filter from = 1 to <= 3.
SELECT *
FROM (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY total_amount DESC, order_id DESC
) AS rn
FROM orders o
) ranked
WHERE rn <= 3
ORDER BY customer_id, rn;
customer_id | order_id | total_amount | rn
------------+----------+--------------+----
C001 | O003 | 250000.00 | 1
C001 | O015 | 85000.00 | 2
C001 | O007 | 60000.00 | 3
C002 | O009 | 180000.00 | 1
C002 | O022 | 42000.00 | 2
C002 | O011 | 35000.00 | 3
Why is GROUP BY with MAX wrong for this?
An earlier approach might use GROUP BY with MAX on the date or amount, but that returns only the aggregate value, not the full row.
-- Wrong: returns only customer_id and max date, not the full order row
SELECT customer_id, MAX(created_at) AS latest_date
FROM orders
GROUP BY customer_id;
-- You cannot get order_id, total_amount, or status from this query.
The ROW_NUMBER approach returns the complete row, which is what dashboards and APIs need.
When should you use DENSE_RANK instead?
If ties at the boundary should include all tied rows rather than being split arbitrarily, use DENSE_RANK.
-- Top 3 order amounts per customer, including all ties
SELECT *
FROM (
SELECT o.*,
DENSE_RANK() OVER (
PARTITION BY customer_id
ORDER BY total_amount DESC
) AS tier
FROM orders o
) ranked
WHERE tier <= 3;
-- May return more than 3 rows per customer if amounts tie.
How do you combine both queries?
You can get both the latest and the most expensive in one query using multiple window functions.
WITH ranked AS (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY created_at DESC, order_id DESC
) AS rn_latest,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY total_amount DESC, order_id DESC
) AS rn_expensive
FROM orders o
)
SELECT customer_id, order_id, total_amount, created_at,
CASE WHEN rn_latest = 1 THEN 'Latest' END AS is_latest,
CASE WHEN rn_expensive = 1 THEN 'Most Expensive' END AS is_expensive
FROM ranked
WHERE rn_latest = 1 OR rn_expensive = 1
ORDER BY customer_id;
What about performance and indexing?
A composite index on the partition and sort columns avoids a full sort.
-- For latest order per customer
CREATE INDEX idx_orders_cust_date
ON orders (customer_id, created_at DESC, order_id DESC);
-- For most expensive order per customer
CREATE INDEX idx_orders_cust_amount
ON orders (customer_id, total_amount DESC, order_id DESC);
How do you implement this in Java?
package com.bank.service;
import java.math.BigDecimal;
import java.sql.*;
public class CustomerOrderService {
private final Connection connection;
public CustomerOrderService(Connection connection) {
this.connection = connection;
}
public void printLatestOrderPerCustomer() throws SQLException {
String sql = """
SELECT customer_id, order_id, total_amount, created_at FROM (
SELECT o.*, ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY created_at DESC, order_id DESC
) AS rn FROM orders o
) ranked WHERE rn = 1
""";
try (Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
String customerId = rs.getString("customer_id");
BigDecimal amount = rs.getBigDecimal("total_amount");
System.out.printf("Customer %s: Latest order Rs.%s%n", customerId, amount);
}
}
}
}
What are common interview follow-up questions?
- "How do you get both the latest and the most expensive in one query?" Use two
ROW_NUMBERfunctions with differentORDER BYclauses in the same CTE. - "What if there are no orders for a customer?" The customer will not appear in the result. Use a
LEFT JOINfrom customers to the ranked orders if you need all customers. - "How do you paginate within each group?" Use
rn BETWEEN offset+1 AND offset+pageSize.
Visual Overview
Customer C001's orders: ┌──────────┬──────────────┬────────────┐ │ order_id │ total_amount │ created_at │ ├──────────┼──────────────┼────────────┤ │ O003 │ 250,000 │ 2024-01-10 │ │ O007 │ 60,000 │ 2024-02-20 │ │ O015 │ 85,000 │ 2024-03-15 │ └──────────┴──────────────┴────────────┘ ROW_NUMBER by date DESC: ROW_NUMBER by amount DESC: ┌──────────┬────┐ ┌──────────┬────┐ │ O015 │ 1 │ ← latest │ O003 │ 1 │ ← most expensive │ O007 │ 2 │ │ O015 │ 2 │ │ O003 │ 3 │ │ O007 │ 3 │ └──────────┴────┘ └──────────┴────┘ Filter rn = 1: Filter rn = 1: → O015 (latest order) → O003 (most expensive order) Filter rn <= 3: → All three orders (top-3 per customer)
Key Takeaway
For "one row per group" problems, the tool is ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ...) filtered on the computed number in an outer query. For the latest order per customer, order by created_at DESC, order_id DESC and keep row number one, returning the full order row rather than just a timestamp. For the most expensive order, keep the same shape but order by total_amount DESC, order_id DESC and keep row number one. For the top three per group, change the filter from equals one to less than or equal to three.
In the National Bank of India relationship-manager dashboard, Rajesh needed each customer's latest order and single biggest order, plus their top three by amount. An earlier version used GROUP BY with MAX on the timestamp, which returned only the timestamp and not the full order row, so the dashboard could not link to the actual order. The ROW_NUMBER approach returns the whole row, which fixed it. The id DESC tie-breaker makes results deterministic when two orders share a timestamp, and composite indexes on the partition and sort columns keep these queries fast.
The senior distinction is ROW_NUMBER versus RANK and DENSE_RANK: ROW_NUMBER gives exactly N rows per group and is arbitrary on ties unless a tie-breaker is added, while DENSE_RANK includes everyone tied at the cutoff. For a fair "top 3 including everyone tied at third," switch to DENSE_RANK.
Remember one sentence: ROW_NUMBER partitioned by the group, ordered by the key, filtered to 1 for the top row or N for the top N.
---