SQL Window Functions - Understanding ROW_NUMBER()

Introduction

ROW_NUMBER() is a window function that assigns a unique number to each row within a specified partition of a result set. Think of it like numbering items in different lists, where each list can start numbering from 1.

Why ROW_NUMBER() Matters?

It’s useful when you need to:

• Number rows sequentially
• Find the first or last entry in a group
• Remove duplicates
• Create unique identifiers for rows

Main Content: Understanding ROW_NUMBER()

Let’s use this simple table:

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

-- Insert sample data
INSERT INTO student_scores VALUES
('Alice', 'Math', 85),
('Alice', 'Science', 92),
('Bob', 'Math', 78),
('Bob', 'Science', 85),
('Charlie', 'Math', 90),
('Charlie', 'Science', 88);

Example 1: Basic ROW_NUMBER()

Let’s number all scores in descending order:

SELECT 
    ROW_NUMBER() OVER (ORDER BY score DESC) as rank,
    student_name,
    subject,
    score
FROM student_scores;

This simple query:

1. Orders scores from highest to lowest
2. Assigns unique numbers starting from 1
3. Numbers all rows sequentially

Results:

rank | student_name | subject | score
-----|--------------|---------|-------
1    | Alice        | Science | 92
2    | Charlie      | Math    | 90
3    | Alice        | Math    | 85
4    | Bob          | Science | 85
5    | Charlie      | Science | 88
6    | Bob          | Math    | 78

Example 2: ROW_NUMBER() with PARTITION BY

Let’s number scores within each subject:

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

This query:

1. Groups rows by subject
2. Numbers each subject’s scores separately
3. Orders scores within each subject

Results:

subject | subject_rank | student_name | score
--------|--------------|--------------|-------
Math    | 1           | Charlie      | 90
Math    | 2           | Alice        | 85
Math    | 3           | Bob          | 78
Science | 1           | Alice        | 92
Science | 2           | Charlie      | 88
Science | 3           | Bob          | 85

Example 3: Finding Top Scores

Let’s find the highest score in each subject:

SELECT *
FROM (
    SELECT 
        subject,
        student_name,
        score,
        ROW_NUMBER() OVER (
            PARTITION BY subject 
            ORDER BY score DESC
        ) as score_rank
    FROM student_scores
) ranked
WHERE score_rank = 1;

This query:

1. Numbers scores within each subject
2. Keeps only the top scores (rank = 1)
3. Shows the highest scorer per subject

Results:

subject | student_name | score | score_rank
--------|--------------|-------|------------
Math    | Charlie      | 90    | 1
Science | Alice        | 92    | 1

Best Practices

1. Ordering

   • Always include ORDER BY in ROW_NUMBER()
   • Choose a column that gives a clear sequence
   • Use multiple columns if needed for uniqueness

2. Partitioning

   • Use PARTITION BY when you need separate numbering
   • Keep partitions logical and meaningful
   • Don’t create too many small partitions

3. Column Selection

   • Include relevant columns for context
   • Use clear column aliases
   • Order output for readability

4. Common Uses

   • Numbering rows within groups
   • Finding top N records
   • Removing duplicates
   • Creating sequential IDs

5. Things to Remember

   • ROW_NUMBER() always returns unique numbers
   • Numbers are sequential (1, 2, 3, …)
   • Order matters for the numbering sequence

Remember:

• ROW_NUMBER() gives unique, sequential numbers
• PARTITION BY creates separate numbering groups
• ORDER BY determines the numbering sequence
• Numbers always start from 1

Common Mistakes to Avoid:

1. Forgetting ORDER BY
2. Confusing with RANK() or DENSE_RANK()
3. Not considering NULL values in ordering
4. Creating too complex partitions

Happy querying! 🚀