Using RANK for Pagination - Breaks Page Navigation

-- RANK creates gaps that break pagination

WITH paginated AS (

SELECT product_name, price,

RANK() OVER(ORDER BY price DESC) as page_position

FROM products

)

SELECT * FROM paginated

WHERE page_position BETWEEN 21 AND 40; -- Page 2

-- PROBLEM: If 10 products tie at rank 15, ranks 16-25 don't exist!

-- ROW_NUMBER guarantees sequential positions

WITH paginated AS (

SELECT product_name, price,

ROW_NUMBER() OVER(ORDER BY price DESC, product_id) as page_position

FROM products

)

SELECT * FROM paginated

WHERE page_position BETWEEN 21 AND 40; -- Page 2

-- SUCCESS: Always gets exactly 20 items per page

Pagination requires exactly N items per page with positions 1,2,3...N. RANK can create gaps (1,2,2,4,5) that break this logic. ROW_NUMBER always gives sequential numbers.

Always use ROW_NUMBER for pagination, unique IDs, and sequential numbering

High

RANK skips numbers after ties, breaking "20 items per page" logic

Using ROW_NUMBER for Competitions - Ignores Meaningful Ties

-- ROW_NUMBER ignores ties in competitions

SELECT athlete_name, score,

ROW_NUMBER() OVER(ORDER BY score DESC) as final_rank

FROM competition_results;

-- PROBLEM: Two athletes with same score get different ranks arbitrarily

-- RANK handles ties fairly in competitions

SELECT athlete_name, score,

RANK() OVER(ORDER BY score DESC, age ASC) as final_rank

FROM competition_results;

-- SUCCESS: Tied athletes get same rank, next rank skips appropriately

In competitions, tied performers should receive the same rank. ROW_NUMBER breaks ties arbitrarily, which is unfair. RANK gives tied values the same rank.

Use RANK for competitions, contests, and any scenario where ties should be recognized

Medium

ROW_NUMBER assigns arbitrary ranks to tied competitors

Missing Tie-Breakers in ORDER BY

-- Incomplete ORDER BY causes inconsistent results