SQL Ranking Functions

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- Basic Syntax
DENSE_RANK() OVER (
    [PARTITION BY partition_expression, ...]
    ORDER BY sort_expression [ASC | DESC], ...
)
 
-- Minimal form
DENSE_RANK() OVER (ORDER BY column_name)
 
-- Full form with partitioning
DENSE_RANK() OVER (
    PARTITION BY category_column
    ORDER BY score DESC
)
 
-- Syntax is identical to RANK() and ROW_NUMBER()
-- The semantic difference is in tie handling and gap behavior

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- Sales data with multiple ties
CREATE TABLE quarterly_sales (
    salesperson_id INT PRIMARY KEY,
    salesperson_name VARCHAR(100),
    region VARCHAR(50),
    total_sales DECIMAL(12,2)
);
 
INSERT INTO quarterly_sales VALUES
(1, 'Alice', 'North', 250000),
(2, 'Bob', 'North', 220000),
(3, 'Carol', 'North', 250000),   -- Ties with Alice
(4, 'David', 'North', 180000),
(5, 'Emma', 'North', 220000),    -- Ties with Bob
(6, 'Frank', 'North', 220000),   -- Three-way tie!
(7, 'Grace', 'North', 150000),
(8, 'Henry', 'North', 150000);   -- Ties with Grace
 
-- Compare all three ranking functions
SELECT 
    salesperson_name,
    total_sales,
    ROW_NUMBER() OVER (ORDER BY total_sales DESC) AS row_num,
    RANK() OVER (ORDER BY total_sales DESC) AS rank_val,
    DENSE_RANK() OVER (ORDER BY total_sales DESC) AS dense_rank_val
FROM quarterly_sales
ORDER BY total_sales DESC, salesperson_name;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- Assign products to exactly 5 price tiers
-- DENSE_RANK ensures all tiers 1-5 have products
WITH price_ranked AS (
    SELECT 
        product_id,
        product_name,
        unit_price,
        DENSE_RANK() OVER (ORDER BY unit_price DESC) AS price_tier
    FROM products
),
max_tier AS (
    SELECT MAX(price_tier) AS max_t FROM price_ranked
)
SELECT 
    pr.product_name,
    pr.unit_price,
    pr.price_tier AS original_tier,
    -- Normalize to exactly 5 tiers (1-5)
    CEIL(pr.price_tier * 5.0 / mt.max_t) AS normalized_tier
FROM price_ranked pr
CROSS JOIN max_tier mt
ORDER BY unit_price DESC;
 
-- DENSE_RANK's consecutive values make tier normalization cleaner
-- If RANK was used, gaps would complicate the mapping

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

-- Verify the density property: all ranks from 1 to max are present
WITH ranked AS (
    SELECT 
        DENSE_RANK() OVER (ORDER BY total_sales DESC) AS dr
    FROM quarterly_sales
),
rank_stats AS (
    SELECT 
        MIN(dr) AS min_rank,
        MAX(dr) AS max_rank,
        COUNT(DISTINCT dr) AS distinct_ranks
    FROM ranked
)
SELECT 
    min_rank,
    max_rank,
    distinct_ranks,
    CASE 
        WHEN distinct_ranks = max_rank - min_rank + 1 
        THEN 'Density verified: no gaps' 
        ELSE 'Gap found (should never happen)' 
    END AS verification
FROM rank_stats;
 
-- Compare distinct ranks vs distinct ORDER BY values
SELECT 
    COUNT(DISTINCT total_sales) AS distinct_sales_values,
    MAX(DENSE_RANK() OVER (ORDER BY total_sales DESC)) AS max_dense_rank,
    CASE 
        WHEN COUNT(DISTINCT total_sales) = MAX(DENSE_RANK() OVER (ORDER BY total_sales DESC))
        THEN 'Max rank equals distinct value count'
        ELSE 'Mismatch (should never happen)'
    END AS value_count_verification
FROM quarterly_sales;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- Find the 3rd highest salary (handles ties correctly)
-- If multiple people share 3rd highest, return all of them
WITH salary_ranked AS (
    SELECT 
        employee_id,
        employee_name,
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT 
    employee_name,
    salary
FROM salary_ranked
WHERE salary_rank = 3;
 
-- Why DENSE_RANK is correct here:
-- If salaries are: 100K, 90K, 90K, 80K, 70K
-- RANK gives: 1, 2, 2, 4, 5 → 3rd highest doesn't exist!
-- DENSE_RANK gives: 1, 2, 2, 3, 4 → 3rd highest is 80K
-- The question asks for "3rd highest VALUE", not "3rd position"

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- Assign customers to loyalty tiers based on spending
-- Tier 1 = highest spenders, Tier 2 = next level, etc.
WITH spending_ranked AS (
    SELECT 
        customer_id,
        customer_name,
        total_spending,
        DENSE_RANK() OVER (ORDER BY total_spending DESC) AS spending_rank
    FROM customer_spending
),
tier_mapping AS (
    SELECT 
        customer_id,
        customer_name,
        total_spending,
        spending_rank,
        CASE 
            WHEN spending_rank = 1 THEN 'Platinum Elite'
            WHEN spending_rank = 2 THEN 'Platinum'
            WHEN spending_rank <= 5 THEN 'Gold'
            WHEN spending_rank <= 10 THEN 'Silver'
            ELSE 'Bronze'
        END AS loyalty_tier
    FROM spending_ranked
)
SELECT * FROM tier_mapping ORDER BY spending_rank;
 
-- DENSE_RANK ensures:
-- 1. Customers who spend the same amount get the same tier
-- 2. Tier thresholds work predictably (rank <= 5 means top 5 values)
-- 3. All tier ranks are used - no gaps in the tier logic

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- Find top 3 salary amounts in each department
-- (Not top 3 employees - top 3 distinct salary levels)
WITH salary_levels AS (
    SELECT 
        department,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department 
            ORDER BY salary DESC
        ) AS salary_level
    FROM employees
)
SELECT DISTINCT
    department,
    salary,
    salary_level
FROM salary_levels
WHERE salary_level <= 3
ORDER BY department, salary_level;
 
-- This returns up to 3 rows per department (the 3 highest salary amounts)
-- NOT 3 employees per department
-- Multiple employees can share each salary level

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- Leaderboard with user-friendly rankings
-- Users expect 1st, 2nd, 3rd not 1st, 1st, 3rd
SELECT 
    player_name,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS display_rank,
    CASE DENSE_RANK() OVER (ORDER BY score DESC)
        WHEN 1 THEN '🥇 1st Place'
        WHEN 2 THEN '🥈 2nd Place'
        WHEN 3 THEN '🥉 3rd Place'
        ELSE CONCAT('#', DENSE_RANK() OVER (ORDER BY score DESC))
    END AS formatted_rank
FROM game_scores
WHERE game_id = 'current_season'
ORDER BY display_rank
LIMIT 10;
 
-- For display purposes, DENSE_RANK is often preferred:
-- Users see: 1st, 1st, 2nd, 3rd (all places represented)
-- Instead of: 1st, 1st, 3rd, 4th (skipping 2nd place is confusing)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- Rank products by sales within each category
-- Using all three functions for comparison
SELECT 
    category,
    product_name,
    monthly_sales,
    ROW_NUMBER() OVER (
        PARTITION BY category 
        ORDER BY monthly_sales DESC
    ) AS rn,
    RANK() OVER (
        PARTITION BY category 
        ORDER BY monthly_sales DESC
    ) AS rnk,
    DENSE_RANK() OVER (
        PARTITION BY category 
        ORDER BY monthly_sales DESC
    ) AS drnk
FROM products
ORDER BY category, drnk;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- Select all orders with top 5 order amounts
-- May return more than 5 orders if amounts repeat
WITH amount_ranked AS (
    SELECT 
        order_id,
        customer_id,
        order_amount,
        order_date,
        DENSE_RANK() OVER (ORDER BY order_amount DESC) AS amount_rank
    FROM orders
)
SELECT 
    order_id,
    customer_id,
    order_amount,
    order_date,
    amount_rank
FROM amount_ranked
WHERE amount_rank <= 5
ORDER BY amount_rank, order_date;
 
-- This guarantees:
-- 1. All 5 highest order amounts are included
-- 2. ALL orders with those amounts are included
-- 3. No gaps in the rank sequence (1, 2, 3, 4, 5 all present)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- Analyze performance by salary level
-- Group employees by salary rank (dense) and aggregate
WITH salary_ranked AS (
    SELECT 
        employee_id,
        department,
        salary,
        performance_score,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_level
    FROM employees
)
SELECT 
    salary_level,
    COUNT(*) AS employee_count,
    MIN(salary) AS level_salary,  -- All same in each level
    AVG(performance_score) AS avg_performance,
    MIN(performance_score) AS min_performance,
    MAX(performance_score) AS max_performance
FROM salary_ranked
GROUP BY salary_level
ORDER BY salary_level;
 
-- DENSE_RANK makes salary_level a clean grouping key:
-- Level 1 = highest earners, Level 2 = next, etc.
-- No skipped levels (all 1 to max present)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

-- Two-level ranking: Rank regions, then rank salespeople within regions
WITH region_totals AS (
    SELECT 
        region,
        SUM(total_sales) AS region_sales
    FROM salespeople
    GROUP BY region
),
region_ranked AS (
    SELECT 
        s.*,
        rt.region_sales,
        DENSE_RANK() OVER (ORDER BY rt.region_sales DESC) AS region_rank,
        DENSE_RANK() OVER (
            PARTITION BY s.region 
            ORDER BY s.total_sales DESC
        ) AS rank_in_region
    FROM salespeople s
    JOIN region_totals rt ON s.region = rt.region
)
SELECT 
    region,
    region_sales,
    region_rank,
    salesperson_name,
    total_sales,
    rank_in_region,
    CONCAT(region_rank, '.', rank_in_region) AS composite_rank
FROM region_ranked
ORDER BY region_rank, rank_in_region;
 
-- Composite rank like "1.3" means:
-- Region ranked 1st overall, salesperson ranked 3rd within that region
-- DENSE_RANK ensures both components are gap-free

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- Pitfall 1: Confusing rank types
-- Q: "Who finished 3rd in the race?"
-- Wrong: DENSE_RANK (gives 3rd fastest time, not 3rd position)
-- Right: RANK (gives actual 3rd place)
 
-- Example data: Times 10s, 10s, 12s, 13s
-- DENSE_RANK: 1, 1, 2, 3 → "3rd" is 13s (wrong for position)
-- RANK: 1, 1, 3, 4 → "3rd" is 12s (correct for position)
 
-- Pitfall 2: Expecting fixed row counts
-- Want exactly 3 products per category
SELECT * FROM (
    SELECT 
        category,
        product_name,
        DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS dr
    FROM products
) ranked
WHERE dr <= 3;
-- May return 5 products in a category if 3 tie for 2nd place!
 
-- Solution: Use ROW_NUMBER for exact counts
SELECT * FROM (
    SELECT 
        category,
        product_name,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
    FROM products
) ranked
WHERE rn <= 3;
-- Guarantees exactly 3 per category