T-SQL 2005 Enhancements - Ranking - RANK(), DENSE_RANK() and NTILE()

The other day, I introduced the first of the new ranking functions - ROW_NUMBER().  Today I want to touch on the other three of these.

• RANK() - gives the position of a value within a set, leaving gaps where there are ties
• DENSE_RANK() - gives the position of a value within the set, leaving no gaps between groups
• NTILE() - partitions the groups into equal groups

For the code to build the tables we are going to use, get the table create from the first entry in this 2 part series:
http://spaces.msn.com/members/drsql/Blog/cns!1pqLLlQgw9hZKLV1K4eLatKw!241.entry

RANK() give us the position in the set, much like you would expect when you rank sports teams by record.  There may be 10 first place teams, but the 11th team is in 11th place.  Everything works the same as ROWNUMBER, so we won't spend any time discussing how to order these by different values, or how to use PARTITION. 

select value, RANK() over (order by value) as RANK
from  ranking

value      RANK
-----------  --------------------
1           1
1           1
2           3
2           3
3           5
3           5
3           5
3           5
3           5
3           5

With DENSE_RANK, it does not leave gaps, so the 2nd group will be numbered 2, no matter how many values there are in the first group:

select value, DENSE_RANK() over (order by value) as DENSE_RANK
from  ranking

value      DENSE_RANK
-----------  --------------------
1           1
1           1
2           2
2           2
3           3
3           3
3           3
3           3
3           3
3           3

Finally, NTILE() can be used to split the set into equal groups.  For example, if you want to split the data into 2 equal groups, you could do:

select value, NTILE(2) over (order by value) as NTILE
from  ranking

value     NTILE
----------- --------------------
1           1
1           1
2           1
2           1
3           1
3           2
3           2
3           2
3           2
3           2

Or 3:

select value, NTILE(3) over (order by value) as NTILE
from  ranking

value       NTILE
----------- --------------------
1           1
1           1
2           1
2           1
3           2
3           2
3           2
3           3
3           3
3           3

Or 4 (just kidding! Hopefully it is clear by now, or you will probably never get it :))  If you put a value greater than the number of values in the set, you will end up with a slightly weird result:

select value, NTILE(200) over (order by value) as NTILE
from  ranking
value               NTILE
-------------------- --------------------
1                    1
1                    2
2                    3
2                    4
3                    5
3                    6
3                    7
3                    8
3                    9
3                    10

So if had been thinking, hmm, I could calculate percentages of values within the set very easy with this, you sure can, but you would have to have at least 100 rows in the result set.  The maximum value for the parameter of NTILE is the maximum of bigint, though currently in the April CTP, it seems to be just the max of int.  So no more than two billion sets there my friends.

Next time, I think perhaps we will look at INTERSECT and EXCEPT, two really nice little clauses to add to the old tool bag....