Greatest-N-Per-Group with ROW_NUMBER/RANK/DENSE_RANK

• Greatest-N-per-group – the pattern of selecting the top N rows within each group (e.g., top 3 transactions per account) rather than globally.
• Global top-N mistake – ORDER BY amount DESC LIMIT 3 sorts the entire table and keeps the first 3 rows across all groups, which might all come from one group.
• Per-group ranking – ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY amount DESC) numbers rows 1, 2, 3, ... restarting at 1 for each account.
• Outer filter – WHERE rn <= 3 in the outer query keeps the top 3 per group, because window functions cannot appear in WHERE directly.
• Tie handling – ROW_NUMBER gives exactly N rows; RANK/DENSE_RANK include all rows tied at the cutoff, potentially returning more than N.

What is the greatest-N-per-group pattern?

The greatest-N-per-group pattern selects the top N rows within each group rather than globally. Examples include the latest transaction per account, the top 3 earners per branch, or the most expensive order per customer. It is one of the most common SQL interview questions and production query patterns.

-- Goal: top 2 transactions by amount per account
-- This is a greatest-N-per-group problem with N=2

Why is ORDER BY ... LIMIT N wrong for per-group results?

A global ORDER BY amount DESC LIMIT N sorts the entire result and keeps the first N rows overall. It answers "the top N in the whole table," not "the top N in each group."

-- Wrong: global top 3 transactions (not per-account)
SELECT * FROM transactions
ORDER BY amount DESC
LIMIT 3;

txn_id | account_id | amount
-------+------------+--------
T099   | SAV2001    | 500000    ← all from
T045   | SAV2001    | 480000    ← the same
T078   | SAV2001    | 450000    ← account!

At National Bank of India, this bug once produced the two biggest transactions in the whole bank, both from the same wealthy account, when the report was supposed to show the top transactions per account.

How do you solve it with ROW_NUMBER?

ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY amount DESC) numbers rows within each account, restarting at 1 for each partition. Then filter WHERE rn <= N in the outer query.

-- Correct: top 2 transactions per account
SELECT *
FROM (
    SELECT t.*,
           ROW_NUMBER() OVER (
               PARTITION BY account_id
               ORDER BY amount DESC, txn_id DESC
           ) AS rn
    FROM transactions t
) ranked
WHERE rn <= 2
ORDER BY account_id, rn;

account_id | txn_id | amount     | rn
-----------+--------+------------+----
CUR3001    | T045   | 250000.00  |  1
CUR3001    | T046   | 180000.00  |  2
SAV2001    | T099   | 500000.00  |  1
SAV2001    | T078   | 450000.00  |  2

The txn_id DESC tie-breaker ensures deterministic results when two transactions have the same amount.

When do you use RANK or DENSE_RANK instead?

RANK and DENSE_RANK include all rows tied at the cutoff, so the result can exceed N rows per group. This is the fair behavior when ties matter.

-- DENSE_RANK: top 2 amount tiers per account (includes all ties)
SELECT *
FROM (
    SELECT t.*,
           DENSE_RANK() OVER (
               PARTITION BY account_id
               ORDER BY amount DESC
           ) AS tier
    FROM transactions t
) ranked
WHERE tier <= 2;

If three transactions tie at the second-highest amount, all three are included. With ROW_NUMBER, only one of the three would survive.

| Function | Exactly N rows per group? | Handles ties? |
|----------|--------------------------|---------------|
| ROW_NUMBER | Yes | Breaks ties arbitrarily |
| RANK | No (can exceed N) | Ties share rank, then skip |
| DENSE_RANK | No (can exceed N) | Ties share rank, no skip |

How do you use a CTE instead of a subquery?

CTEs make the query more readable, especially when combining multiple per-group calculations.

WITH ranked_txns AS (
    SELECT t.*,
           ROW_NUMBER() OVER (
               PARTITION BY account_id
               ORDER BY amount DESC, txn_id DESC
           ) AS rn
    FROM transactions t
)
SELECT txn_id, account_id, amount, txn_date
FROM ranked_txns
WHERE rn <= 3;

What about the special case of exactly 1 per group?

The single-row-per-group case is by far the most common: the latest transaction per account, the most expensive order per customer. It uses the same pattern with rn = 1.

-- Latest transaction per account
SELECT *
FROM (
    SELECT t.*,
           ROW_NUMBER() OVER (
               PARTITION BY account_id
               ORDER BY txn_date DESC, txn_id DESC
           ) AS rn
    FROM transactions t
) ranked
WHERE rn = 1;

Some databases offer shortcuts: PostgreSQL has DISTINCT ON, and lateral joins can achieve the same result. But the window function approach is the portable standard.

What about performance and indexing?

A composite index on the partition and sort columns avoids a full sort. For the top-N-per-account query, the ideal index is:

CREATE INDEX idx_txn_account_amount
    ON transactions (account_id, amount DESC, txn_id DESC);

This lets the engine produce sorted partitions directly from the index. For large tables at National Bank of India with millions of transactions, this reduced the query from seconds to milliseconds.

How do you implement this in Java?

package com.bank.service;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TopTransactionService {

    private final Connection connection;

    public TopTransactionService(Connection connection) {
        this.connection = connection;
    }

    public List<String> getTopNPerAccount(int topN) throws SQLException {
        String sql = """
            SELECT account_id, txn_id, amount, rn FROM (
                SELECT t.account_id, t.txn_id, t.amount,
                       ROW_NUMBER() OVER (
                           PARTITION BY t.account_id
                           ORDER BY t.amount DESC, t.txn_id DESC
                       ) AS rn
                FROM transactions t
            ) ranked WHERE rn <= ?
            ORDER BY account_id, rn
            """;
        List<String> results = new ArrayList<>();
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, topN);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    String acct = rs.getString("account_id");
                    BigDecimal amount = rs.getBigDecimal("amount");
                    int rn = rs.getInt("rn");
                    results.add(String.format("%s #%d: Rs.%s", acct, rn, amount));
                }
            }
        }
        return results;
    }
}

What are common mistakes?

The most common mistake is using GROUP BY with MAX when you need the full row.

-- Wrong: GROUP BY returns only the max amount, not the full row
SELECT account_id, MAX(amount) AS max_amount
FROM transactions
GROUP BY account_id;
-- You get the amount but not the txn_id, date, or other columns.

-- Correct: ROW_NUMBER returns the full row
SELECT * FROM (
    SELECT t.*,
           ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY amount DESC, txn_id DESC) AS rn
    FROM transactions t
) ranked WHERE rn = 1;

Another mistake is forgetting the subquery wrapper and trying to filter the window function directly in WHERE.

What are common interview follow-up questions?

- "How would you get the bottom N instead of top N?" Change the `ORDER BY` direction (ASC instead of DESC).
- "What if N varies per group?" Use a join to a configuration table and compare `rn <= config.topN`.
- "How do you paginate within each group?" Use `rn BETWEEN offset+1 AND offset+pageSize`.