RANK and DENSE_RANK

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Compare gap-producing and gap-free ranks when values tie.

Run the example

SELECT EmployeeID, AnnualSalary,
       RANK() OVER (ORDER BY AnnualSalary DESC) AS SalaryRank,
       DENSE_RANK() OVER (ORDER BY AnnualSalary DESC) AS DenseSalaryRank
FROM dbo.Employees
ORDER BY AnnualSalary DESC, EmployeeID;

Read it step by step

1. Both functions assign the same rank to equal salary values.
2. RANK skips following numbers after a tie; DENSE_RANK does not.
3. EmployeeID only breaks ties in final display, not inside either ranking window.

Check the result

Inspect equal salaries to see where the two rank columns diverge.

Practice

Rank the salaries of Employees 6, 7, 10, and 11 with both functions. Where does a gap appear?

SELECT EmployeeID, AnnualSalary,
       RANK() OVER (ORDER BY AnnualSalary DESC) AS SalaryRank,
       DENSE_RANK() OVER (ORDER BY AnnualSalary DESC) AS DenseSalaryRank
FROM dbo.Employees
WHERE EmployeeID IN (6, 7, 10, 11)
ORDER BY AnnualSalary DESC, EmployeeID;