Skip to content
SQLSimplified
Window Functions

RANK() Per Group With PARTITION BY

Rank employees by salary within each department using RANK() OVER (PARTITION BY ...).

Overview

RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) assigns a rank within each department, leaving gaps after ties (two employees tied for 1st push the next rank to 3rd). It's the companion to DENSE_RANK and ideal for "top earner per department" style reports where gaps are acceptable.

The Query

Loading playground environment...

Step-by-Step Breakdown

  1. RANK() OVER (...): assigns a rank based on the window ordering.
  2. PARTITION BY department_id: the ranking restarts at 1 for each department.
  3. ORDER BY salary DESC: highest-paid in each department gets rank 1.

RANK vs DENSE_RANK

RANK leaves gaps after ties (1,1,3); DENSE_RANK doesn't (1,1,2). Pick based on whether the gap matters for your ranking semantics.

Variations

Rank movies by budget within each genre:

Loading playground environment...

Common Mistakes

  • Forgetting PARTITION BY. Without it, RANK ranks everyone company-wide instead of per department.
  • Expecting no gaps. RANK intentionally skips numbers after ties.
  • Filtering RANK in WHERE. Window functions run after WHERE; wrap in a CTE/subquery to filter on dept_rank.

Related examples

DENSE_RANK(): Ranking Without Gaps
Window Functions
Rank employees by salary company-wide using DENSE_RANK, which never skips numbers after ties.
ROW_NUMBER(): Enumerating Rows Within Groups
Window Functions
Assign a sequential number to each employee within their department, ordered by salary.
Window Functions: RANK() and PARTITION BY
Window Functions
Rank employees by salary within their own department using RANK() OVER (PARTITION BY ...), without collapsing rows the way GROUP BY does.

Cite this resource

SQLSimplified. "RANK() Per Group With PARTITION BY". Available at: https://sqlsimplified.online/examples/rank-per-group