ROW_NUMBER, RANK and DENSE_RANK

Sequence numbers differ from tied ranks.

UnderstandStudy an examplePractise

Understand the concept

ROW_NUMBER assigns unique sequence numbers within each partition. RANK gives ties the same rank and leaves a gap; DENSE_RANK leaves no gap. Two employees with equal salaries can receive different row numbers but matching ranks.

For deterministic ROW_NUMBER results, add a unique tie-breaker. Adding that key to a RANK ordering also breaks salary ties, so distinguish the top three records from the top three distinct salary levels.

NTILE distributes a partition into groups with approximately equal row counts. Equal measured values may fall into different buckets, so it is not equivalent to fixed statistical percentile thresholds.

WHEN TO USE IT

Use them for first rows per group, leaderboards and deduplication.

COMMON PITFALL

Interchanging ROW_NUMBER and RANK without defining tie behaviour.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT FullName, ROW_NUMBER() OVER (ORDER BY Salary DESC, EmployeeId) AS Position FROM lab.Employee;

Produces unique positions 1–5.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Find employees in the top two distinct salary levels.

Show the solution and explanation

Worked solution

WITH R AS (SELECT FullName, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS r FROM lab.Employee) SELECT * FROM R WHERE r <= 2;

Rasim and Ada. Any ties at these levels would be preserved.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.