ROW_NUMBER, RANK, DENSE_RANK — Ranking Rows

These three functions assign a number to each row based on ordering. They look similar but behave differently when there are ties. This lesson will make the difference crystal clear with a single example table.

The Difference in One Table

Let us rank students by their exam score. Notice what happens when two students have the same score (a tie).

See the differences? Amit and Sneha both scored 90. ROW_NUMBER gives them different numbers (2, 3) — it never repeats. RANK gives them both 2, then skips to 4 for Rahul — it leaves a gap. DENSE_RANK gives them both 2, then continues with 3 for Rahul — no gap.

Quick Summary of Differences

• ROW_NUMBER — Always unique. No ties. Arbitrary tiebreaker. 1, 2, 3, 4, 5
• RANK — Allows ties. Skips numbers after ties. 1, 2, 2, 4, 5
• DENSE_RANK — Allows ties. No gaps. 1, 2, 2, 3, 4

The SQL

-- Compare all three ranking functions side by side
SELECT
  student_name,
  score,
  ROW_NUMBER() OVER(ORDER BY score DESC) AS row_num,
  RANK()       OVER(ORDER BY score DESC) AS rank_val,
  DENSE_RANK() OVER(ORDER BY score DESC) AS dense_rank_val
FROM students;

-- QA scenario: Rank orders by amount per customer
SELECT
  customer_id,
  order_id,
  total_amount,
  ROW_NUMBER() OVER(
    PARTITION BY customer_id
    ORDER BY total_amount DESC
  ) AS amount_rank
FROM orders;

The Killer Pattern: Get the Latest Record Per Group

This is the single most useful window function pattern. You want the most recent order per customer. Or the latest login per user. Or the highest-scoring test per student. ROW_NUMBER with PARTITION BY and a subquery does it.

-- Most recent order per customer
SELECT * FROM (
  SELECT
    customer_id,
    order_id,
    order_date,
    total_amount,
    ROW_NUMBER() OVER(
      PARTITION BY customer_id
      ORDER BY order_date DESC
    ) AS rn
  FROM orders
) ranked
WHERE rn = 1;

-- Latest login per user
SELECT * FROM (
  SELECT
    user_id,
    login_at,
    ip_address,
    ROW_NUMBER() OVER(
      PARTITION BY user_id
      ORDER BY login_at DESC
    ) AS rn
  FROM login_history
) ranked
WHERE rn = 1;