ROW_NUMBER, RANK, DENSERANK and NTILE


I had an interesting discussion with some members of a class I am teaching right now about how paging is done.  It looks like there are four Functions that allow for paging and using Common Table Expressions.  They all can optionally include a separate ORDER BY clause to specify how they are sorted (so your result set can be sorted differently than it is ranked).

ROW_NUMBER()

This function returns the exact number of the row number in the result.  For example:

WITH SegmentedSalesPeople AS 
(
  SELECT FirstName, LastName, SalesYTD, ROW_NUMBER() as RowNum
  FROM Sales.vSalesPerson
  WHERE SalesYTD <> 0 
)
SELECT * 
FROM SegmentedSalesPeople
WHERE RowNum BETWEEN 1 AND 5
ORDER BY SalesYTD

Or if you want to order the rows by SalesYTD and sort it by Lastname instead:

WITH SegmentedSalesPeople AS 
(
  SELECT FirstName, LastName, SalesYTD, (ROW_NUMBER() OVER (ORDER BY SalesYTD)) as RowNum
  FROM Sales.vSalesPerson
  WHERE SalesYTD <> 0 
)
SELECT * 
FROM SegmentedSalesPeople
WHERE RowNum BETWEEN 1 AND 5
ORDER BY LastName

RANK()

This function returns the ranking of the row in the result.  Rows with identical results in the ordering will return the same rank (e.g. Two sales people with sales of $1,000 will get the same ranking). For example:

WITH SegmentedSalesPeople AS 
(
  SELECT FirstName, LastName, SalesYTD, RANK() as Ranking
  FROM Sales.vSalesPerson
  WHERE SalesYTD <> 0 
)
SELECT * 
FROM SegmentedSalesPeople
WHERE Ranking BETWEEN 1 AND 5
ORDER BY SalesYTD

Or if you want to order the rows by SalesYTD and sort it by Lastname instead:

WITH SegmentedSalesPeople AS 
(
  SELECT FirstName, LastName, SalesYTD, (RANK() OVER (ORDER BY SalesYTD)) as Ranking
  FROM Sales.vSalesPerson
  WHERE SalesYTD <> 0 
)
SELECT * 
FROM SegmentedSalesPeople
WHERE Ranking BETWEEN 1 AND 5
ORDER BY LastName

DENSE_RANK()

Same as RANK above except there will not be any gaps (e.g. If rank 3rd and 4th are tied, they will both be RANK'd 3.  With DENSE_RANK the next row will be ranked 4 instead of 5 (as RANK would do).  For example:

WITH SegmentedSalesPeople AS 
(
  SELECT FirstName, LastName, SalesYTD, DENSE_RANK() as DenseRanking
  FROM Sales.vSalesPerson
  WHERE SalesYTD <> 0 
)
SELECT * 
FROM SegmentedSalesPeople
WHERE DenseRanking BETWEEN 1 AND 5
ORDER BY SalesYTD

Or if you want to order the rows by SalesYTD and sort it by Lastname instead:

WITH SegmentedSalesPeople AS 
(
  SELECT FirstName, LastName, SalesYTD, (DENSE_RANK() OVER (ORDER BY SalesYTD)) as DenseRanking
  FROM Sales.vSalesPerson
  WHERE SalesYTD <> 0 
)
SELECT * 
FROM SegmentedSalesPeople
WHERE DenseRanking BETWEEN 1 AND 5
ORDER BY LastName

NTILE(x)

Separates the result into x number of batches. When you specify NTILE you must specify the number of 'tiles' to create.  For example:

WITH SegmentedSalesPeople AS 
(
  SELECT FirstName, LastName, SalesYTD, NTILE(5) as PageNumber
  FROM Sales.vSalesPerson
  WHERE SalesYTD <> 0 
)
SELECT * 
FROM SegmentedSalesPeople
WHERE PageNumber = 1
ORDER BY SalesYTD

Or if you want to order the rows by SalesYTD and sort it by Lastname instead:

WITH SegmentedSalesPeople AS 
(
  SELECT FirstName, LastName, SalesYTD, (NTILE(5) OVER (ORDER BY SalesYTD)) as PageNumber
  FROM Sales.vSalesPerson
  WHERE SalesYTD <> 0 
)
SELECT * 
FROM SegmentedSalesPeople
WHERE PageNumber = 1
ORDER BY LastName

This is fun!

 



Shawn
Shawn Wildermuth
Author, Teacher, and Coach




My Courses

Wilder Minds Training
Vue.js by Example (New Lower Price)
Bootstrap 4 by Example (New Lower Price)
Intro to Font Awesome 5 (Free Course)
Pluralsight
Building an API with ASP.NET Core (New Course)
Building a Web App with ASP.NET Core, MVC6, EF Core, Bootstrap and Angular (updated for 2.2)
Less: Getting Started (New)
Using Visual Studio Code for ASP.NET Core Projects
Implementing ASP.NET Web API

Application Name WilderBlog Environment Name Production
Application Ver v4.0.30319 Runtime Framework x86
App Path D:\home\site\wwwroot\ Runtime Version .NET Core 4.6.27514.02
Operating System Microsoft Windows 10.0.14393 Runtime Arch X86