RANK is a window function that assigns a ranking to rows based on an ordering condition. When used with JOINs, it can rank related records within business groups.
Ranking is common in sales, performance, finance, and customer analysis.
Rank employees by salary within each department.
SELECT
e.EmployeeName,
d.DepartmentName,
e.Salary,
RANK() OVER (
PARTITION BY d.DepartmentID
ORDER BY e.Salary DESC
) AS SalaryRank
FROM Employees e
INNER JOIN Departments d
ON e.DepartmentID = d.DepartmentID;Used in employee performance reports, sales rankings, product analysis, and financial reporting.
- RANK assigns positions to rows.
- PARTITION BY can create rankings within groups.
- JOIN can provide the grouping information.
- Tied values receive the same rank.
- Ranking is useful for comparative analysis.
How can RANK be used with JOIN?
RANK can assign positions to records after related information has been combined through a JOIN.
What happens when two rows have the same ranking value?
They receive the same rank, and subsequent ranking numbers may contain gaps.