ReviseAlgo Logo

Window Functions

ROW_NUMBER, RANK, and DENSE_RANK

Applying sequential numerical values and rankings to rows.

Last Updated: June 12, 2026 • 14 min read

ROW_NUMBER, RANK, and DENSE_RANK are window functions that assign sequential numbers to rows within a partition. They differ in how they handle ties: ROW_NUMBER gives unique numbers, RANK skips numbers after ties, and DENSE_RANK never skips. Mastering these three functions unlocks top-N queries, pagination, and leaderboards.

1. Introduction

Ranking functions are among the most-used window functions in SQL. They answer questions like "Who are the top 3 earners per department?" and "What rank is this student in their class?" Combined with PARTITION BY and ORDER BY, they let you compute rankings within any group — without self-joins or correlated subqueries.

2. Why It Matters

  • Top-N per group: "Show the 3 best-selling products per category" — a pattern impossible without window functions in standard SQL.
  • Pagination: ROW_NUMBER enables efficient offset-based pagination in complex queries.
  • Leaderboards: RANK and DENSE_RANK handle ties naturally — essential for game scores, sports rankings, and academic standings.
  • Deduplication: ROW_NUMBER is the standard technique for keeping only the latest row per entity (e.g., latest order per customer).

3. Real-World Analogy

ROW_NUMBER = Race bib numbers. Every runner gets a unique number (1, 2, 3, 4...) based on finish time, even if two runners cross the line simultaneously.

RANK = Olympic medals. Two runners tie for gold (both rank 1), next runner is rank 3 (not 2 — the silver is skipped).

DENSE_RANK = School grades. Two students tie for the top score (both rank 1), next student is rank 2 (no skipping).

4. How It Works

5. Internal Architecture

6. Visual Explanation

7. Practical Example

Top 3 earners per department:

Deduplicate: keep only the latest order per customer:

8. Common Mistakes

Using window functions in WHERE

Window functions execute after WHERE, so WHERE RANK() OVER (...) = 1 is invalid. Wrap in a CTE or subquery first, then filter in the outer query.

ROW_NUMBER with ties is non-deterministic

When two rows have the same ORDER BY value, ROW_NUMBER assigns them arbitrary numbers. Add a tiebreaker (e.g., ORDER BY salary DESC, id) for deterministic results.

Interview Insight

"When would you use DENSE_RANK instead of RANK?" — When gaps in ranking numbers are unacceptable. Example: a leaderboard where ranks 1, 1, 2, 3 are clearer than 1, 1, 3, 4. DENSE_RANK is also used in "find the Nth highest salary" queries.

9. Quick Quiz

Q1: For salaries [90, 90, 80, 70], what does RANK() OVER (ORDER BY salary DESC) return?

Answer: 1, 1, 3, 4. Two rows tie at rank 1, then rank 2 is skipped, and the next value is rank 3. DENSE_RANK would return 1, 1, 2, 3.

Q2: How do you find the second-highest salary in each department?

Answer: Use DENSE_RANK: WITH r AS (SELECT *, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dr FROM employees) SELECT * FROM r WHERE dr = 2.

10. Scenario-Based Challenge

Challenge: E-Commerce Best Sellers Report

For each product category, show the top 5 products by total revenue. For tied products, show all tied ones (use RANK, not ROW_NUMBER).

Requirements:

  1. JOIN products with order_items to compute total revenue per product.
  2. Use RANK() OVER (PARTITION BY category ORDER BY revenue DESC).
  3. Filter for rank <= 5 in an outer query.
  4. Add a column showing each product's revenue as a % of category total.

11. Debugging Exercise

Find the bug in this query:

Issues:

  1. Window function in WHERE: rnk is computed after WHERE runs. Fix: wrap in a CTE, then filter: WITH r AS (...) SELECT * FROM r WHERE rnk <= 3.
  2. Non-deterministic without tiebreaker: If two employees have the same salary, RANK ties them but the next rank skips. Add ORDER BY salary DESC, name for consistent ordering.

12. Interview Questions

Q1: Find the Nth highest salary in the company.

A: WITH r AS (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr FROM employees) SELECT DISTINCT salary FROM r WHERE dr = N. Using DENSE_RANK handles ties correctly.

Q2: What's the difference between ROW_NUMBER and RANK?

A: ROW_NUMBER always assigns unique sequential numbers (1, 2, 3, 4) — ties get arbitrary ordering. RANK assigns the same number to ties and skips subsequent numbers (1, 1, 3, 4). Use ROW_NUMBER for pagination/dedup, RANK for leaderboards.

Q3: Can you use ROW_NUMBER for pagination?

A: Yes. WITH paged AS (SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM products) SELECT * FROM paged WHERE rn BETWEEN 21 AND 40 gives page 2 with 20 items per page.

13. Production Considerations

  • Always add a tiebreaker: ORDER BY salary DESC, id ensures deterministic ROW_NUMBER results across executions.
  • Index partition and order columns: A composite index on (department, salary DESC) lets PostgreSQL avoid sorting for window function computation.
  • Use keyset pagination over OFFSET: For large tables, WHERE id > last_seen_id LIMIT 20 is much faster than OFFSET-based pagination with ROW_NUMBER.
  • CTE for filtering: Always wrap ranking in a CTE/subquery before applying WHERE — window functions can't appear in WHERE directly.
  • Prefer keyset pagination for infinite scroll: ROW_NUMBER with OFFSET degrades on large tables (O(N) scan to skip rows). Keyset pagination is O(1) with an index.