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!