What is the difference between RANK, DENSE_RANK, and ROW_NUMBER?

• ROW_NUMBER() – assigns a strictly unique sequential integer (1, 2, 3 ...) to every row in the partition, even when rows tie under the ORDER BY; the tie is broken arbitrarily unless you add a secondary sort column.
• RANK() – gives tied rows the same rank and then skips the next numbers to account for the ties, producing gaps (e.g. 1, 1, 3 when two rows tie for first).
• DENSE_RANK() – gives tied rows the same rank but never skips numbers, so the ranking is contiguous (e.g. 1, 1, 2 when two rows tie for first).
• All three use an OVER (PARTITION BY ... ORDER BY ...) clause, restart at 1 for each partition, and cannot be used directly in WHERE – you must wrap them in a subquery or CTE.
• Choice rule – use ROW_NUMBER for exactly N rows per group, RANK for competition-style ranking with gaps, and DENSE_RANK for contiguous tier numbering.

What is ROW_NUMBER?

ROW_NUMBER() assigns a strictly increasing unique integer to every row within each partition. Even if two rows share the same ordering value, they receive different numbers. Which tied row gets the lower number is not deterministic unless you add a tie-breaker column to the ORDER BY.

At the National Bank of India, Rajesh needs the single most recent transaction per account on the dashboard. He uses ROW_NUMBER because he wants exactly one row per account, never two.

-- 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;

The txn_id DESC tie-breaker ensures a deterministic result when two transactions share the same date.

What is RANK?

RANK() gives tied rows the same rank and then skips the next numbers. If two rows tie for rank 2, the next row is rank 4, not rank 3. The gap equals the number of tied rows minus one. This matches competition-style ranking where tied competitors share a position.

-- Rank branches by quarterly profit (competition style)
SELECT branch_name, quarterly_profit,
       RANK() OVER (ORDER BY quarterly_profit DESC) AS rnk
FROM branches;

branch_name       | quarterly_profit | rnk
------------------+------------------+-----
Mumbai Central    | 5000000.00       |   1
Delhi Connaught   | 4200000.00       |   2
Chennai Anna Ngr  | 4200000.00       |   2
Kolkata Park St   | 3800000.00       |   4   ← skips 3

What is DENSE_RANK?

DENSE_RANK() also gives tied rows the same rank but never skips a number. After two rows tied at rank 2, the next row is rank 3. The numbering is contiguous with no gaps, which is useful when you care about distinct ranking levels rather than absolute positions.

-- Top 3 distinct salary tiers among employees
SELECT emp_name, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS tier
FROM employees
WHERE branch_id = 'BR001';

emp_name   | salary      | tier
-----------+-------------+------
Pankaj     | 250000.00   |    1
Rajesh     | 200000.00   |    2
Priya      | 200000.00   |    2
Anita      | 180000.00   |    3   ← no gap

Filtering WHERE tier <= 3 returns all employees in the top three salary levels, including both Rajesh and Priya.

How do the three functions differ from each other?

The only difference is how they handle rows that tie under the ORDER BY. All three use the same OVER (PARTITION BY ... ORDER BY ...) syntax, restart at 1 for each partition, and produce an integer column.

| Scenario: two rows tie at position 2 | ROW_NUMBER | RANK | DENSE_RANK |
|---------------------------------------|-----------|------|------------|
| First tied row                        | 2         | 2    | 2          |
| Second tied row                       | 3         | 2    | 2          |
| Next row after the tie                | 4         | 4    | 3          |
| Gaps in numbering?                    | Never     | Yes  | Never      |
| Guaranteed exactly N rows for <= N?   | Yes       | No   | No         |

-- Side-by-side comparison on account balances
SELECT account_id, balance,
       ROW_NUMBER() OVER (ORDER BY balance DESC) AS row_num,
       RANK()       OVER (ORDER BY balance DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY balance DESC) AS dense_rnk
FROM accounts
WHERE branch_id = 'BR001';

account_id | balance     | row_num | rnk | dense_rnk
-----------+-------------+---------+-----+-----------
SAV2001    | 500000.00   |       1 |   1 |         1
CUR3001    | 350000.00   |       2 |   2 |         2
SAV2005    | 350000.00   |       3 |   2 |         2
CUR3009    | 200000.00   |       4 |   4 |         3

When should you use each function?

Use ROW_NUMBER when you need exactly N rows per group. The classic use case is the latest transaction per account or pagination within a partition. Always add a deterministic tie-breaker such as the primary key so results are stable across runs.

Use RANK when you want competition-style ranking where ties share a position and the next position reflects how many rows were ahead. This is appropriate for leaderboards where the gap matters.

Use DENSE_RANK when you care about distinct value levels rather than absolute positions. For example, "show me customers in the top three spending tiers" where you want three tiers regardless of how many customers share each tier.

-- Use case: exactly 1 row per account (ROW_NUMBER)
SELECT * FROM (
    SELECT t.*, ROW_NUMBER() OVER (
        PARTITION BY account_id ORDER BY txn_date DESC, txn_id DESC
    ) AS rn FROM transactions t
) x WHERE rn = 1;

-- Use case: all employees tied at top-3 salary tiers (DENSE_RANK)
SELECT * FROM (
    SELECT e.*, DENSE_RANK() OVER (
        PARTITION BY branch_id ORDER BY salary DESC
    ) AS tier FROM employees e
) x WHERE tier <= 3;

Why can you not use these functions directly in WHERE?

Window functions are computed at the SELECT step of query processing, after WHERE and GROUP BY have already filtered and grouped the rows. Because WHERE runs before SELECT, you cannot reference a window function alias in WHERE. The standard solution is to wrap the query in a subquery or CTE and filter the computed column in the outer query.

-- Wrong: window function in WHERE
SELECT * FROM transactions
WHERE ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY txn_date DESC) = 1;
-- ERROR: window functions are not allowed in WHERE

-- Correct: wrap in subquery
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;

What about deterministic tie-breaking?

When two rows share the same ordering value, ROW_NUMBER picks one arbitrarily. The result can change between runs or after a database restart, which makes reports non-reproducible. The fix is to add a unique column such as the primary key to the ORDER BY.

-- Non-deterministic: two transactions on the same date get arbitrary order
ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY txn_date DESC)

-- Deterministic: txn_id breaks the tie
ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY txn_date DESC, txn_id DESC)

For RANK and DENSE_RANK, ties are the intended behavior, so a tie-breaker would defeat the purpose. Only add a tie-breaker when you want to eliminate ties.

What about performance and indexing?

All three functions require the database to sort rows within each partition. Without an index, the database performs a full sort. A composite index on the partition and order columns lets the engine produce the sorted partitions efficiently.

-- Index for ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY txn_date DESC)
CREATE INDEX idx_txn_account_date
    ON transactions (account_id, txn_date DESC);

-- Index for DENSE_RANK() OVER (PARTITION BY branch_id ORDER BY salary DESC)
CREATE INDEX idx_emp_branch_salary
    ON employees (branch_id, salary DESC);

For large tables at National Bank of India with millions of transactions, these indexes reduced the top-N-per-account query from a full table scan to an index-only scan.

What are common mistakes?

The most common mistake is using ROW_NUMBER when ties should be preserved.

-- Wrong: top 3 earners per branch, but ties at #3 are split arbitrarily
SELECT * FROM (
    SELECT e.*, ROW_NUMBER() OVER (
        PARTITION BY branch_id ORDER BY salary DESC
    ) AS rn FROM employees e
) x WHERE rn <= 3;
-- If 4 employees tie for 3rd salary, only one is included.

-- Correct: use DENSE_RANK to include everyone in the top 3 salary tiers
SELECT * FROM (
    SELECT e.*, DENSE_RANK() OVER (
        PARTITION BY branch_id ORDER BY salary DESC
    ) AS tier FROM employees e
) x WHERE tier <= 3;

Another mistake is forgetting that ROW_NUMBER without a deterministic tie-breaker produces unstable results.

How is this handled in Java with JDBC?

The ranking is computed entirely in SQL. The Java code simply executes the query and processes the result set.

package com.bank.service;

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

public class AccountRankingService {

    private final Connection connection;

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

    public List<String> getTopAccountsByBalance(String branchId, int topN) throws SQLException {
        String sql = """
            SELECT account_id, balance, dense_rnk FROM (
                SELECT account_id, balance,
                       DENSE_RANK() OVER (ORDER BY balance DESC) AS dense_rnk
                FROM accounts WHERE branch_id = ?
            ) ranked WHERE dense_rnk <= ?
            """;
        List<String> results = new ArrayList<>();
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, branchId);
            ps.setInt(2, topN);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    String accountId = rs.getString("account_id");
                    BigDecimal balance = rs.getBigDecimal("balance");
                    int rank = rs.getInt("dense_rnk");
                    results.add(String.format("Rank %d: %s – Rs.%s", rank, accountId, balance));
                }
            }
        }
        return results;
    }
}

What are common interview follow-up questions?

- "If two employees tie for rank 3, how many rows does `WHERE rnk <= 3` return with RANK vs DENSE_RANK?" With `RANK`, both tied rows have rank 3 so both are included, returning 4 rows. With `DENSE_RANK`, both have rank 3 and the next is rank 4, returning the same 4 rows. The difference shows when the tie is at rank 1 or 2.
- "How do you get exactly 5 rows per group regardless of ties?" Use `ROW_NUMBER` with a deterministic tie-breaker.
- "How do you get the top 3 distinct salary levels including all employees at each level?" Use `DENSE_RANK() <= 3`.