ROW_NUMBER, NTILE, RANK And DENSE_RANK

Yesterday I showed how to do ranking in SQL Server 2000, today we will look at how it's done in SQL Server 2005

CREATE TABLE Rankings (Value Char(1),id INT)
INSERT INTO Rankings
SELECT 'A',1 UNION ALL
SELECT 'A',3 UNION ALL
SELECT 'B',3 UNION ALL
SELECT 'B',4 UNION ALL
SELECT 'B',5 UNION ALL
SELECT 'C',2 UNION ALL
SELECT 'D',6 UNION ALL
SELECT 'E',6 UNION ALL
SELECT 'F',5 UNION ALL
SELECT 'F',9 UNION ALL
SELECT 'F',10



ROW_NUMBER()
This will just add a plain vanilla row number

SELECT ROW_NUMBER() OVER( ORDER BY Value ) AS 'rownumber',*
FROM Rankings


The following one is more interesting, besides the rownumber the Occurance field contains the row number count for a given value
That happens when you use PARTITION with ROW_NUMBER

SELECT ROW_NUMBER() OVER( ORDER BY value ) AS 'rownumber',
ROW_NUMBER() OVER(PARTITION BY value ORDER BY ID ) AS 'Occurance',*
FROM Rankings
ORDER BY 1,2


This is just ordered in alphabetical order descending

SELECT ROW_NUMBER() OVER( ORDER BY Value DESC) AS 'rownumber',*
FROM Rankings

RANK()
Rank will skip numbers if there are duplicate values

SELECT RANK() OVER ( ORDER BY Value),*
FROM Rankings


DENSE_RANK()
DENSE_RANK will not skip numbers if there are duplicate values

SELECT DENSE_RANK() OVER ( ORDER BY Value),*
FROM Rankings


NTILE()
NTILE splits the set in buckets
So for 11 values we do something like this: 11/2 =5 + 1 remainder, the first 6 rows get 1 the next 5 rows get 2
If we use NTILE(3) we would have something like this: 11/3 =3 + 2 remainders, so 3 buckets of 3 and the first 2 buckets will get 1 of the remainders each

SELECT NTILE(2) OVER ( ORDER BY Value ),*
FROM Rankings

SELECT NTILE(3) OVER ( ORDER BY Value ),*
FROM Rankings