ROW_NUMBER, RANK and DENSE_RANK

ROW_NUMBER, RANK and DENSE_RANK

Verified Tie Dataset

The advanced_scores table deliberately gives Meera and Sana the same 92 marks in X-A. That tie exposes the real difference between the three functions.

Side-by-Side Ranking Query

SELECT student_name, class_name, marks,
       ROW_NUMBER() OVER (
         PARTITION BY class_name
         ORDER BY marks DESC, student_id
       ) AS row_no,
       RANK() OVER (
         PARTITION BY class_name
         ORDER BY marks DESC
       ) AS rank_no,
       DENSE_RANK() OVER (
         PARTITION BY class_name
         ORDER BY marks DESC
       ) AS dense_rank_no
FROM advanced_scores
ORDER BY class_name, row_no;

ROW_NUMBER adds student_id as a deterministic tie-breaker. RANK and DENSE_RANK order only by marks so equal marks remain peers.

Verified Ranking Output

RANK skips position 2 in X-A after the two tied leaders; DENSE_RANK assigns Aarav 2. ROW_NUMBER still assigns distinct row positions 1, 2 and 3.

Deterministic Ordering and Tie Policy

A ranking report must state whether equal marks share a position and how fixed-row selections break ties.

• All tied winners: filter RANK or DENSE_RANK <= N.
• Exactly N rows: filter ROW_NUMBER <= N using a documented tie-breaker.
• Do not add a unique key to RANK unless you intentionally want to destroy peer ties.
• Top-level ORDER BY is still needed for final display order.

Top Two Rows per Class

WITH ranked AS (
  SELECT student_id, student_name,
         class_name, marks,
         ROW_NUMBER() OVER (
           PARTITION BY class_name
           ORDER BY marks DESC, student_id
         ) AS row_no
  FROM advanced_scores
)
SELECT student_name, class_name, marks, row_no
FROM ranked
WHERE row_no <= 2
ORDER BY class_name, row_no;

A window value cannot be filtered in WHERE at the same query level, so the CTE calculates it first. Replace ROW_NUMBER with RANK if all ties at the Nth position must be included, even when more than N rows result.

Deduplication and Pagination Patterns

Keep the latest record per student

WITH latest AS (
  SELECT log_id, student_id, event_time,
         ROW_NUMBER() OVER (
           PARTITION BY student_id
           ORDER BY event_time DESC, log_id DESC
         ) AS row_no
  FROM attendance_log
)
SELECT log_id, student_id, event_time
FROM latest
WHERE row_no = 1;

The unique log_id tie-breaker makes “latest” deterministic. Review before deleting older duplicates. For pagination, ROW_NUMBER can label a stable ordered snapshot, but live data changes can shift pages; keyset pagination is often safer for frequently changing applications.

Performance, Mistakes and Practice

• Partition and ordering may require sorting; inspect EXPLAIN.
• Project only required columns into the ranking stage.
• Indexing may help filtering and access, but it does not eliminate every window sort.
• Never use ROW_NUMBER without deterministic ordering when reproducibility matters.
• Test ties at first, Nth and last positions.

Practice: return all class winners with RANK; return top two distinct score bands with DENSE_RANK; return exactly two students with ROW_NUMBER; add a tie at X-B second place and compare result counts.

Official References

• MySQL 8.4: Window Function Descriptions
• MySQL 8.4: Window Concepts and Syntax
• MySQL 8.4: Window Function Optimization

Syntax and behavior were checked against the official MySQL 8.4 manual. Verify plans and limits on your own server before production use.