ROW_NUMBER, RANK, DENSE_RANK and NTILE in SQL Server

Marks : 95, 90, 90, 85
  ROW_NUMBER : 1, 2, 3, 4   -> always unique (ties broken randomly unless ORDER BY is unique)
  RANK       : 1, 2, 2, 4   -> ties share rank, then a GAP
  DENSE_RANK : 1, 2, 2, 3   -> ties share rank, NO gap
  NTILE(2)   : 1, 1, 2, 2   -> split rows into N buckets

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Topic table: CompetitionScores (with deliberate ties)

DROP TABLE IF EXISTS dbo.CompetitionScores;

CREATE TABLE dbo.CompetitionScores
(
    ParticipantID INT IDENTITY(1,1) PRIMARY KEY,
    Participant   VARCHAR(50) NOT NULL,
    School        VARCHAR(50) NOT NULL,
    Event         VARCHAR(20) NOT NULL,
    Score         INT         NOT NULL,
    SubmittedAt   DATETIME2   NOT NULL
);

INSERT INTO dbo.CompetitionScores (Participant, School, Event, Score, SubmittedAt) VALUES
('Aarav',   'DPS',       'Quiz',   95, '2025-05-01 10:00'),
('Diya',    'DPS',       'Quiz',   90, '2025-05-01 10:05'),
('Ishaan',  'KV',        'Quiz',   90, '2025-05-01 09:58'),
('Kiara',   'KV',        'Quiz',   85, '2025-05-01 10:10'),
('Vihaan',  'Narayana',  'Quiz',   85, '2025-05-01 10:12'),
('Anaya',   'Narayana',  'Quiz',   80, '2025-05-01 10:01'),
('Reyansh', 'DPS',       'Quiz',   70, '2025-05-01 10:20'),
('Aarav',   'DPS',       'Coding', 88, '2025-05-02 11:00'),
('Diya',    'DPS',       'Coding', 92, '2025-05-02 11:30'),
('Ishaan',  'KV',        'Coding', 92, '2025-05-02 11:10'),
('Kiara',   'KV',        'Coding', 75, '2025-05-02 11:40'),
('Myra',    'Narayana',  'Coding', 99, '2025-05-02 11:50'),
('Anaya',   'Narayana',  'Coding', 75, '2025-05-02 11:05');
GO

(13 rows affected)

Part A: Side by Side Comparison

SELECT Participant, Event, Score,
       ROW_NUMBER() OVER (ORDER BY Score DESC) AS RowNum,
       RANK()       OVER (ORDER BY Score DESC) AS RankNo,
       DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRankNo,
       NTILE(4)     OVER (ORDER BY Score DESC) AS Quartile
FROM dbo.CompetitionScores
WHERE Event = 'Quiz';

Same with PARTITION BY Event: ranking restarts for each event

SELECT Event, Participant, Score,
       ROW_NUMBER() OVER (PARTITION BY Event ORDER BY Score DESC)              AS RowNum,
       RANK()       OVER (PARTITION BY Event ORDER BY Score DESC)              AS RankNo,
       DENSE_RANK() OVER (PARTITION BY Event ORDER BY Score DESC)              AS DenseRankNo,
       -- Tie breaker : whoever submitted earlier wins
       ROW_NUMBER() OVER (PARTITION BY Event ORDER BY Score DESC, SubmittedAt) AS FairRowNum
FROM dbo.CompetitionScores
ORDER BY Event, Score DESC;

Employee salaries (55000, 70000 are tied in the data)

SELECT FirstName, Salary,
       ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum,
       RANK()       OVER (ORDER BY Salary DESC) AS RankNo,
       DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRankNo
FROM dbo.Employees
ORDER BY Salary DESC;

Part B: TOP-N per Group (most common real use)

Highest paid employee per department (exactly 1 row each -> ROW_NUMBER)

WITH r AS
(
    SELECT DepartmentID, FirstName, Salary,
           ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC, EmployeeID) AS rn
    FROM dbo.Employees
    WHERE DepartmentID IS NOT NULL
)
SELECT DepartmentID, FirstName, Salary FROM r WHERE rn = 1;

Top 2 salaries per department Including ties -> DENSE_RANK

WITH r AS
(
    SELECT DepartmentID, FirstName, Salary,
           DENSE_RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS dr
    FROM dbo.Employees
)
SELECT * FROM r WHERE dr <= 2 ORDER BY DepartmentID, dr;

Nth highest salary (N = 3) - DENSE_RANK version (best answer in interviews)

WITH r AS (SELECT DISTINCT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS dr FROM dbo.Employees)
SELECT Salary AS ThirdHighestSalary FROM r WHERE dr = 3;

Top 3 customers by revenue in each customer segment

WITH CustRev AS
(
    SELECT c.Segment, c.CustomerName, SUM(oi.LineTotal) AS Revenue
    FROM dbo.Customers c
    JOIN dbo.Orders o      ON o.CustomerID = c.CustomerID
    JOIN dbo.OrderItems oi ON oi.OrderID   = o.OrderID
    GROUP BY c.Segment, c.CustomerName
),
Ranked AS
(
    SELECT *, RANK() OVER (PARTITION BY Segment ORDER BY Revenue DESC) AS rnk FROM CustRev
)
SELECT * FROM Ranked WHERE rnk <= 3 ORDER BY Segment, rnk;

Latest order of each customer

WITH r AS
(
    SELECT o.*, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC, OrderID DESC) AS rn
    FROM dbo.Orders o
)
SELECT CustomerID, OrderID, OrderDate, Status FROM r WHERE rn = 1;

Part C: Other Uses of ROW_NUMBER

Pagination: page 2, page size 10 (ROW_NUMBER way)

DECLARE @PageNo INT = 2, @PageSize INT = 10;

WITH Paged AS
(
    SELECT OrderID, CustomerID, OrderDate,
           ROW_NUMBER() OVER (ORDER BY OrderDate DESC, OrderID DESC) AS rn
    FROM dbo.Orders
)
SELECT * FROM Paged
WHERE rn BETWEEN (@PageNo - 1) * @PageSize + 1 AND @PageNo * @PageSize;

Same with OFFSET / FETCH (modern way)

SELECT OrderID, CustomerID, OrderDate
FROM dbo.Orders
ORDER BY OrderDate DESC, OrderID DESC
OFFSET (@PageNo - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;

Number the orders of each customer: 1st order, 2nd order, ...

SELECT CustomerID, OrderID, OrderDate,
       ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate, OrderID) AS OrderSequence
FROM dbo.Orders
ORDER BY CustomerID, OrderSequence;

Gaps and islands: find streaks of consecutive sale days in March 2025

WITH Days AS (SELECT DISTINCT SaleDate FROM dbo.Sales WHERE SaleDate >= '2025-03-01' AND SaleDate < '2025-04-01'),
     Grp  AS (SELECT SaleDate,
                     DATEADD(DAY, -ROW_NUMBER() OVER (ORDER BY SaleDate), SaleDate) AS GroupKey
              FROM Days)
SELECT MIN(SaleDate) AS StreakStart, MAX(SaleDate) AS StreakEnd, COUNT(*) AS DaysInStreak
FROM Grp
GROUP BY GroupKey
ORDER BY StreakStart;

Part D: NTILE - split into buckets

Divide customers into 4 revenue quartiles

WITH CustRev AS
(
    SELECT o.CustomerID, SUM(oi.LineTotal) AS Revenue
    FROM dbo.Orders o JOIN dbo.OrderItems oi ON oi.OrderID = o.OrderID
    GROUP BY o.CustomerID
)
SELECT c.CustomerName, cr.Revenue,
       NTILE(4) OVER (ORDER BY cr.Revenue DESC) AS Quartile,
       CASE NTILE(4) OVER (ORDER BY cr.Revenue DESC)
            WHEN 1 THEN 'Platinum' WHEN 2 THEN 'Gold' WHEN 3 THEN 'Silver' ELSE 'Bronze' END AS Band
FROM CustRev cr JOIN dbo.Customers c ON c.CustomerID = cr.CustomerID;

PERCENT_RANK and CUME_DIST (relative position 0..1)

SELECT FirstName, Salary,
       CAST(PERCENT_RANK() OVER (ORDER BY Salary) AS DECIMAL(5,3)) AS PercentRank,
       CAST(CUME_DIST()    OVER (ORDER BY Salary) AS DECIMAL(5,3)) AS CumeDist
FROM dbo.Employees;

Practice Questions

1. Find the 2nd highest score in each event (handle ties with DENSE_RANK).
2. Find the top selling product (by quantity) in every month of 2025 (use Sales).
3. Show the first order of every customer and the product categories in it.
4. Rank sales people by 2025 revenue within each Region.
5. Remove duplicate rows from a copy of CompetitionScores keeping the highest score per participant & event.