12.3 ROW_NUMBER()

Level: 12 | Version: v1.1 | Author: Meptrasoft

Overview

ROW_NUMBER() assigns a unique number to every row based on the specified order.

Unlike RANK() and DENSE_RANK(), two rows never receive the same row number.

ROW_NUMBER()
→ UNIQUE NUMBER FOR EVERY ROW

ROW_NUMBER() → No ties

Practice Table

This topic reuses the employee table from 12.1.

Example: ROW_NUMBER()

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS row_no
FROM employee;

Result:

Even though Andrew and Brian have the same salary, their row numbers are different.

ROW_NUMBER() → Every row gets a unique number.

How It Works

PARTITION BY department
        ↓
Separate Finance and Sales
        ↓
ORDER BY salary DESC
        ↓
Assign row numbers

For Finance:

50000 → 1
50000 → 2
20000 → 3

The exact order of tied rows should not be relied upon unless the ORDER BY includes an additional tie-breaker.

For example:

ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC, name
)

This gives the database another column to determine the order of tied salaries.

RANK vs DENSE_RANK vs ROW_NUMBER

RANK       → 1, 1, 3
DENSE_RANK → 1, 1, 2
ROW_NUMBER → 1, 2, 3

ROW_NUMBER() never assigns the same number to two rows within the window.

Common Uses

ROW_NUMBER() is commonly used for:

• Numbering rows
• Finding the top N rows per group
• Selecting the first/latest record
• Removing duplicate records using a ranking rule

Example:

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY department
               ORDER BY salary DESC
           ) AS row_no
    FROM employee
) t
WHERE row_no = 1;

This returns the highest-salary employee from each department.

Common Beginner Mistakes

Placement Quick Points

ROW_NUMBER()
→ UNIQUE NUMBER

TIE
→ NO SHARED NUMBER

PARTITION BY
→ SEPARATE GROUPS

ORDER BY
→ DEFINE NUMBERING ORDER

• ROW_NUMBER() assigns a unique sequential number to each row.
• Tied values still receive different row numbers.
• PARTITION BY can restart numbering for each group.
• ORDER BY determines the numbering order.
• Add a tie-breaker when a deterministic order is required.

Interview Questions

What is ROW_NUMBER()?

ROW_NUMBER() assigns a unique sequential number to each row within the window.

How does ROW_NUMBER() handle ties?

Tied values still receive different row numbers.

What is the difference between RANK() and ROW_NUMBER()?

TEXT

RANK       → Ties share a rank
ROW_NUMBER → Every row gets a unique number

Can ROW_NUMBER() use PARTITION BY?

Yes. It can restart numbering for each partition.

Why add another column to ORDER BY?

To provide a tie-breaker and make the row-number assignment deterministic when primary ordering values are equal.

Practice & Hands-On Exercises

Using the employee table:

1. Assign row numbers to all employees by salary descending:

SELECT name, department, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_row_no
FROM employee;

2. Assign row numbers separately for each department:

SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_row_no
FROM employee;

3. Observe how tied salaries are numbered.
4. Compare ROW_NUMBER() with RANK().
5. Add a second ORDER BY column and observe the effect:

SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, name ASC) AS tie_broken_row_no
FROM employee;

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

ROW_NUMBER()
      ↓
UNIQUE SEQUENTIAL NUMBER
      ↓
NO TIES

ROW_NUMBER() assigns a unique number to every row, even when multiple rows have the same ordering value.

End of ROW_NUMBER()