SQL Window Functions - Understanding RANK and DENSE_RANK

Introduction

RANK and DENSE_RANK are window functions that assign ranking numbers to rows. The main difference is how they handle ties:

• RANK leaves gaps after ties
• DENSE_RANK doesn’t leave gaps

Why RANK and DENSE_RANK Matter?

They’re useful when you need to:

• Rank students by scores
• Rank products by sales
• Find top performers
• Handle tied values properly

Main Content: Understanding RANK and DENSE_RANK

Let’s use this simple table:

-- Create a simple table
CREATE TABLE exam_scores (
    student_name VARCHAR(50),
    subject VARCHAR(50),
    score INT
);

-- Insert sample data
INSERT INTO exam_scores VALUES
('Alice', 'Math', 95),
('Bob', 'Math', 95),
('Charlie', 'Math', 90),
('David', 'Math', 85),
('Eve', 'Math', 85),
('Frank', 'Math', 80);

Example 1: Basic RANK vs DENSE_RANK

Let’s see how both functions handle the same data:

SELECT 
    student_name,
    score,
    RANK() OVER (ORDER BY score DESC) as rank_number,
    DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank_number
FROM exam_scores;

This query shows:

1. How RANK handles ties (leaves gaps)
2. How DENSE_RANK handles ties (no gaps)
3. The difference between both rankings

Results:

student_name | score | rank_number | dense_rank_number
-------------|-------|-------------|------------------
Alice        | 95    | 1          | 1
Bob          | 95    | 1          | 1
Charlie      | 90    | 3          | 2
David        | 85    | 4          | 3
Eve          | 85    | 4          | 3
Frank        | 80    | 6          | 4

Notice:

• Alice and Bob tie for rank 1
• RANK skips to 3 after the tie
• DENSE_RANK uses 2 for the next different score

Example 2: Using PARTITION BY

Let’s rank scores within different subjects:

-- Add more data for different subjects
INSERT INTO exam_scores VALUES
('Alice', 'Science', 88),
('Bob', 'Science', 88),
('Charlie', 'Science', 85),
('David', 'Science', 82);

SELECT 
    subject,
    student_name,
    score,
    RANK() OVER (
        PARTITION BY subject 
        ORDER BY score DESC
    ) as subject_rank
FROM exam_scores;

This query:

1. Groups by subject
2. Ranks scores within each subject
3. Starts ranking from 1 in each group

Results:

subject  | student_name | score | subject_rank
---------|--------------|-------|-------------
Math     | Alice        | 95    | 1
Math     | Bob          | 95    | 1
Math     | Charlie      | 90    | 3
Math     | David        | 85    | 4
Math     | Eve          | 85    | 4
Math     | Frank        | 80    | 6
Science  | Alice        | 88    | 1
Science  | Bob          | 88    | 1
Science  | Charlie      | 85    | 3
Science  | David        | 82    | 4

Example 3: Comparing All Three Rankings

Let’s see ROW_NUMBER, RANK, and DENSE_RANK together:

SELECT 
    student_name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) as row_num,
    RANK() OVER (ORDER BY score DESC) as rank_num,
    DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank_num
FROM exam_scores
WHERE subject = 'Math';

This query shows:

1. ROW_NUMBER: Always unique (1,2,3,4,5,6)
2. RANK: Shows ties, leaves gaps (1,1,3,4,4,6)
3. DENSE_RANK: Shows ties, no gaps (1,1,2,3,3,4)

Results:

student_name | score | row_num | rank_num | dense_rank_num
-------------|-------|---------|----------|---------------
Alice        | 95    | 1       | 1        | 1
Bob          | 95    | 2       | 1        | 1
Charlie      | 90    | 3       | 3        | 2
David        | 85    | 4       | 4        | 3
Eve          | 85    | 5       | 4        | 3
Frank        | 80    | 6       | 6        | 4

Best Practices

1. Choosing Between RANK and DENSE_RANK

   • Use RANK when gaps matter (competitions)
   • Use DENSE_RANK when gaps don’t matter (grades)
   • Consider your data’s meaning

2. Ordering

   • Always include ORDER BY
   • Consider multiple columns for ties
   • Handle NULL values appropriately

3. Partitioning

   • Use when you need separate rankings
   • Keep partitions logical
   • Don’t create too many small groups

4. Common Uses

   • Competition rankings
   • Academic grading
   • Sales performance
   • Customer segmentation

5. Things to Remember

   • RANK leaves gaps after ties
   • DENSE_RANK never leaves gaps
   • Both start from 1
   • Both handle ties the same way

Remember:

• RANK: 1,1,3,4,4,6 (with gaps)
• DENSE_RANK: 1,1,2,3,3,4 (no gaps)
• Both are perfect for handling ties

Happy querying! 🚀