Using RANK for Pagination Instead of ROW_NUMBER

-- Trying to paginate with RANK - causes problems with ties

WITH paginated_results AS (

SELECT product_name, price,

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

FROM products

)

SELECT * FROM paginated_results

WHERE page_position BETWEEN 21 AND 40; -- Page 2

-- PROBLEM: If products 15-25 all have same price,

-- they all get rank 15, and ranks 16-25 don't exist!

-- Use ROW_NUMBER for pagination - guarantees unique positions

WITH paginated_results AS (

SELECT product_name, price,

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

FROM products

)

SELECT * FROM paginated_results

WHERE page_position BETWEEN 21 AND 40; -- Page 2

-- SUCCESS: Every product gets unique position 1,2,3,4...

RANK() can have duplicate values and skip numbers after ties. For pagination, you need exactly 20 items per page with positions 1,2,3...20, then 21,22,23...40. RANK() might give you positions like 1,2,2,2,5,6,7... which breaks the "20 items per page" logic. ROW_NUMBER() always gives unique sequential numbers.

Use ROW_NUMBER() for pagination, unique IDs, and any scenario requiring sequential numbering

High

RANK creates gaps after ties, breaking pagination logic

Confusing RANK and DENSE_RANK for Business Logic

-- Using RANK for tier assignments - creates confusing gaps

SELECT student_name, grade,

RANK() OVER(ORDER BY grade DESC) as grade_tier

FROM students;

-- Result: Tier 1, Tier 2, Tier 2, Tier 4, Tier 5

-- PROBLEM: "What happened to Tier 3?" - confusing for users

-- Use DENSE_RANK for continuous tiers/categories

SELECT student_name, grade,

DENSE_RANK() OVER(ORDER BY grade DESC) as grade_tier

FROM students;

-- Result: Tier 1, Tier 2, Tier 2, Tier 3, Tier 4

-- SUCCESS: Continuous tier levels make business sense

RANK is perfect for competitions (1st, 2nd, 2nd, 4th place makes sense), but terrible for business categories. If you're assigning products to "Tier 1, Tier 2, Tier 3" or students to "Grade A, B, C", use DENSE_RANK to avoid confusing gaps. Users expect continuous categories, not missing levels.

Use RANK for competitions/contests, DENSE_RANK for tiers/categories/grades

Medium