Lesson 30 of 54 17 minAdvanced

Window functions: ROW_NUMBER, RANK and NTILE

Number and rank rows without losing them: the OVER clause, ROW_NUMBER, RANK and DENSE_RANK with ties, PARTITION BY for ranking inside groups, the top-N-per-group pattern, removing duplicates, and NTILE for bands.

GROUP BY answers questions about groups, but it collapses the rows: you get one row per group and the details are gone. A window function computes a value over a set of rows (the *window*) and writes it next to every row. Nothing is collapsed.

You can tell a window function by its OVER (…) clause. The simplest one numbers the rows in the order you give:

Query.sql

1SELECT ROW_NUMBER() OVER (ORDER BY Price DESC, ProductId) AS Pos,2       Name, Price3FROM dbo.Products4ORDER BY Pos;

Output

Pos  Name                 Price
---  -------------------  -----
  1  Cordless drill       89.00
  2  Masonry paint 10 L   54.00
  3  Plywood sheet 18 mm  42.00
  4  Claw hammer          18.50
  5  Paint roller set     12.50
  6  Wood screws (200)    11.90
  7  Tape measure 8 m      9.75
  8  Cement 25 kg          7.20
  9  Timber 2x4 2.4 m      6.80
 10  Safety goggles        6.80
 11  Wall plugs (100)      4.50
 12  Sand 25 kg            4.10

The ORDER BY inside OVER says how to number the rows. It is separate from the ORDER BY at the end of the query, which only sorts the result. They are often the same, but they do not have to be.

Ties: ROW_NUMBER, RANK and DENSE_RANK

Timber 2x4 and Safety goggles both cost 6.80. The three numbering functions treat that tie differently:

Query.sql

1SELECT Name, Price,2       ROW_NUMBER() OVER (ORDER BY Price DESC, ProductId) AS RowNum,3       RANK()       OVER (ORDER BY Price DESC)            AS Rnk,4       DENSE_RANK() OVER (ORDER BY Price DESC)            AS DenseRnk5FROM dbo.Products6ORDER BY Price DESC, ProductId;

Output

Name                 Price  RowNum  Rnk  DenseRnk
-------------------  -----  ------  ---  --------
Cordless drill       89.00       1    1         1
Masonry paint 10 L   54.00       2    2         2
Plywood sheet 18 mm  42.00       3    3         3
Claw hammer          18.50       4    4         4
Paint roller set     12.50       5    5         5
Wood screws (200)    11.90       6    6         6
Tape measure 8 m      9.75       7    7         7
Cement 25 kg          7.20       8    8         8
Timber 2x4 2.4 m      6.80       9    9         9
Safety goggles        6.80      10    9         9
Wall plugs (100)      4.50      11   11        10
Sand 25 kg            4.10      12   12        11

Function	On a tie	After a tie
`ROW_NUMBER()`	different numbers (1, 2, 3 …) every row	carries on
`RANK()`	the same number	skips: 9, 9, 11, like a sports table
`DENSE_RANK()`	the same number	no gap: 9, 9, 10

Interactive

Four ways to number rows

Tiebreaker , ProductId for ROW_NUMBERNTILE

SELECT CategoryId, Name, Price,
  ROW_NUMBER() OVER (ORDER BY Price DESC, ProductId),
  RANK()       OVER (ORDER BY Price DESC),
  DENSE_RANK() OVER (ORDER BY Price DESC),
  NTILE(4)     OVER (ORDER BY Price DESC, ProductId)
FROM dbo.Products;

	CategoryId	Name	Price	ROW_NUMBER	RANK	DENSE_RANK	NTILE(4)
1	1	Cordless drill	89.00	1	1	1	1
2	5	Masonry paint 10 L	54.00	2	2	2	1
3	3	Plywood sheet 18 mm	42.00	3	3	3	1
4	1	Claw hammer	18.50	4	4	4	2
5	5	Paint roller set	12.50	5	5	5	2
6	4	Wood screws (200)	11.90	6	6	6	2
7	1	Tape measure 8 m	9.75	7	7	7	3
8	2	Cement 25 kg	7.20	8	8	8	3
9	3	Timber 2x4 2.4 m	6.80	9	9	9	3
10	6	Safety goggles	6.80	10	9	9	4
11	4	Wall plugs (100)	4.50	11	11	10	4
12	2	Sand 25 kg	4.10	12	12	11	4

The highlighted products tie on 6.80. ROW_NUMBER still gives them different numbers (ProductId decides), RANK gives both 9 and skips the next number, DENSE_RANK gives both 9 with no gap. NTILE(4) deals the 12 rows into 4 bands of equal size.

PARTITION BY: start again for every group

PARTITION BY splits the rows into groups, like GROUP BY, but without collapsing them. The numbering restarts at 1 in each group:

Query.sql

1SELECT c.Name AS Category, p.Name, p.Price,2       ROW_NUMBER() OVER (PARTITION BY p.CategoryId3                          ORDER BY p.Price DESC) AS PosInCategory4FROM dbo.Products AS p5JOIN dbo.Categories AS c ON c.CategoryId = p.CategoryId6WHERE p.CategoryId <= 47ORDER BY p.CategoryId, PosInCategory;

Output

Category  Name                 Price  PosInCategory
--------  -------------------  -----  -------------
Tools     Cordless drill       89.00              1
Tools     Claw hammer          18.50              2
Tools     Tape measure 8 m      9.75              3
Building  Cement 25 kg          7.20              1
Building  Sand 25 kg            4.10              2
Timber    Plywood sheet 18 mm  42.00              1
Timber    Timber 2x4 2.4 m      6.80              2
Fixings   Wood screws (200)    11.90              1
Fixings   Wall plugs (100)      4.50              2

Top N per group

“The dearest product in each category” is one of the most common questions in SQL. The obvious attempt fails:

Query.sql

1SELECT CategoryId, Name, Price2FROM dbo.Products3WHERE ROW_NUMBER() OVER (PARTITION BY CategoryId ORDER BY Price DESC) = 1;

Output

Msg 4108, Level 15, State 1, Line 3
Windowed functions can only appear in the SELECT or ORDER BY clauses.

Window functions are computed in the SELECT step, which runs after WHERE (the order you met in the GROUP BY lesson). So WHERE cannot see them. Compute the number in a CTE, and filter in the query that reads it:

Query.sql

1WITH Ranked AS (2    SELECT CategoryId, Name, Price,3           ROW_NUMBER() OVER (PARTITION BY CategoryId ORDER BY Price DESC) AS Pos4    FROM dbo.Products5)6SELECT CategoryId, Name, Price7FROM Ranked8WHERE Pos = 19ORDER BY CategoryId;

Output

CategoryId  Name                 Price
----------  -------------------  -----
         1  Cordless drill       89.00
         2  Cement 25 kg          7.20
         3  Plywood sheet 18 mm  42.00
         4  Wood screws (200)    11.90
         5  Masonry paint 10 L   54.00
         6  Safety goggles        6.80

Change Pos = 1 to Pos <= 2 for the top two, and so on. To keep ties in a top N, filter on RANK instead of ROW_NUMBER. The nine dearest products by ROW_NUMBER would drop one of the two 6.80 products at random; RANK keeps both:

Query.sql

1WITH Ranked AS (2    SELECT Name, Price, RANK() OVER (ORDER BY Price DESC) AS Rnk3    FROM dbo.Products4)5SELECT Rnk, Name, Price6FROM Ranked7WHERE Rnk <= 98ORDER BY Rnk, Name;

Output

Rnk  Name                 Price
---  -------------------  -----
  1  Cordless drill       89.00
  2  Masonry paint 10 L   54.00
  3  Plywood sheet 18 mm  42.00
  4  Claw hammer          18.50
  5  Paint roller set     12.50
  6  Wood screws (200)    11.90
  7  Tape measure 8 m      9.75
  8  Cement 25 kg          7.20
  9  Safety goggles        6.80
  9  Timber 2x4 2.4 m      6.80

Removing duplicates

ROW_NUMBER is also the standard way to delete duplicate rows. Number the rows inside each group of duplicates, keep number 1, and delete the rest. You can DELETE through a CTE that reads a single table:

Query.sql

1CREATE TABLE #Signups (SignupId int, Email nvarchar(100), SignedUp date);2INSERT INTO #Signups VALUES3    (1, N'asha@example.com',  '2024-01-05'),4    (2, N'ben@example.com',   '2024-01-09'),5    (3, N'asha@example.com',  '2024-02-11'),6    (4, N'chloe@example.com', '2024-02-20'),7    (5, N'asha@example.com',  '2024-03-02'),8    (6, N'ben@example.com',   '2024-03-15');9 10WITH Numbered AS (11    SELECT *, ROW_NUMBER() OVER (PARTITION BY Email12                                 ORDER BY SignedUp DESC) AS n13    FROM #Signups14)15DELETE FROM Numbered WHERE n > 1;   -- keeps each email's latest signup16 17SELECT * FROM #Signups ORDER BY SignupId;

Output

SignupId  Email              SignedUp
--------  -----------------  ----------
       4  chloe@example.com  2024-02-20
       5  asha@example.com   2024-03-02
       6  ben@example.com    2024-03-15

NTILE: split into bands

NTILE(n) deals the ordered rows into n groups of (nearly) equal size and returns the group number. 12 products into 4 price bands gives 3 per band:

Query.sql

1SELECT NTILE(4) OVER (ORDER BY Price DESC, ProductId) AS Band,2       Name, Price3FROM dbo.Products4ORDER BY Band, Price DESC;

Output

Band  Name                 Price
----  -------------------  -----
   1  Cordless drill       89.00
   1  Masonry paint 10 L   54.00
   1  Plywood sheet 18 mm  42.00
   2  Claw hammer          18.50
   2  Paint roller set     12.50
   2  Wood screws (200)    11.90
   3  Tape measure 8 m      9.75
   3  Cement 25 kg          7.20
   3  Timber 2x4 2.4 m      6.80
   4  Safety goggles        6.80
   4  Wall plugs (100)      4.50
   4  Sand 25 kg            4.10

When the rows do not divide evenly, the first groups get one row more. 12 rows into 5 bands is 3 + 3 + 2 + 2 + 2:

Query.sql

1SELECT Band, COUNT(*) AS Products, MIN(Price) AS Lowest, MAX(Price) AS Highest2FROM (3    SELECT NTILE(5) OVER (ORDER BY Price DESC, ProductId) AS Band, Price4    FROM dbo.Products5) AS b6GROUP BY Band7ORDER BY Band;

Output

Band  Products  Lowest  Highest
----  --------  ------  -------
   1         3   42.00    89.00
   2         3   11.90    18.50
   3         2    7.20     9.75
   4         2    6.80     6.80
   5         2    4.10     4.50

Quick check

Prices in descending order are 10, 8, 8, 5. What are RANK() and DENSE_RANK() for the 5?

Quick check

Why does WHERE ROW_NUMBER() OVER (…) = 1 fail?

Practice

Show each customer’s most recent order: the customer’s first name, the OrderId and the OrderDate. Only customers who have orders, sorted by first name.

Show a hint

In a CTE, number each customer’s orders with ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC), then keep number 1 and join Customers.

Show the solution

Solution.sql

1WITH Latest AS (2    SELECT CustomerId, OrderId, OrderDate,3           ROW_NUMBER() OVER (PARTITION BY CustomerId4                              ORDER BY OrderDate DESC) AS n5    FROM dbo.Orders6)7SELECT c.FirstName, l.OrderId, l.OrderDate8FROM Latest AS l9JOIN dbo.Customers AS c ON c.CustomerId = l.CustomerId10WHERE l.n = 111ORDER BY c.FirstName;

Output

FirstName  OrderId  OrderDate
---------  -------  ----------
Asha             3  2024-03-01
Ben              9  2024-06-01
Chloe            6  2024-04-20
Dev              7  2024-05-05
Farid           10  2024-06-12
Zoë              8  2024-05-18

Remember

A window function computes over a set of rows and keeps every row: you know it by OVER (…).
ROW_NUMBER numbers 1, 2, 3; RANK repeats on ties and skips; DENSE_RANK repeats without gaps.
Add a unique tiebreaker to the ORDER BY in OVER when ROW_NUMBER must be repeatable.
PARTITION BY restarts the window for each group without collapsing rows.
Window functions cannot go in WHERE: compute them in a CTE and filter outside.
Top N per group: ROW_NUMBER (or RANK to keep ties) in a CTE, then WHERE Pos <= N.
Delete duplicates by numbering inside each group and deleting n > 1 through the CTE.
NTILE(n) deals rows into n bands; the first bands get the extra rows.

Foundations

Creating and changing data

Functions and expressions

Joins and combining tables

Grouping and subqueries

Advanced querying

Programmability

Indexes and performance

Master level

Window functions: ROW_NUMBER, RANK and NTILE

Ties: ROW_NUMBER, RANK and DENSE_RANK

Four ways to number rows

PARTITION BY: start again for every group

Top N per group

Removing duplicates

NTILE: split into bands

Remember