RANK is a window function that assigns rankings to rows based on an ORDER BY expression. Tied values receive the same rank and can create gaps in subsequent rankings.
Ranking is widely used in sales performance, employee analysis, customer segmentation, and competitive reporting.
Rank employees by salary within each department.
SELECT
e.EmployeeName,
d.DepartmentName,
e.Salary,
RANK() OVER (
PARTITION BY e.DepartmentID
ORDER BY e.Salary DESC
) AS SalaryRank
FROM Employees e
INNER JOIN Departments d
ON e.DepartmentID = d.DepartmentID;Used in HR analytics, sales leaderboards, financial analysis, and performance reporting.
- RANK assigns positions within groups.
- PARTITION BY creates separate ranking groups.
- ORDER BY defines ranking order.
- Ties receive the same rank.
- Ties can cause ranking gaps.
How can RANK be used with JOIN?
JOIN can provide descriptive information while RANK calculates the position of each record within its group.
What happens when two rows have the same value with RANK?
They receive the same rank and the next rank can contain a gap.