ROW_NUMBER(): Enumerating Rows Within Groups
Assign a sequential number to each employee within their department, ordered by salary.
Overview
ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) assigns a unique, gap-free
sequence to each row within its partition. Unlike RANK, ties still get
distinct numbers (arbitrary among equals). It's perfect for "pick the Nth row
per group" or deduplicating.
The Query
Employees & Departments
Loading database engine...
Step-by-Step Breakdown
PARTITION BY department_id— numbering restarts at 1 per department.ORDER BY salary DESC— highest-paid getsrn = 1within each department.ROW_NUMBER()guarantees unique values even when salaries tie.
ROW_NUMBER for top-N-per-group
Wrap this in a CTE and filter WHERE rn = 1 to keep only the top-paid
employee of each department.
Variations
Number orders per customer chronologically:
Store (Customers/Orders/Products)
Loading database engine...
Common Mistakes
- Assuming stable ties.
ROW_NUMBERassigns arbitrary order among equalORDER BYvalues — don't rely on which tied row gets which number. - Forgetting PARTITION BY. Without it, numbering spans the whole table.
- Filtering ROW_NUMBER in WHERE. Window functions run after
WHERE; wrap in a subquery/CTE to filter onrn.