While preparing a SQL Server 2008 Transact-SQL course. The ranking capabilities of SQL Server took my attention. From SQL Server 2005 on these powerful functions exist and can be very helpful. For a small demo I created a table with twelve stars and their distance in light-years from the earth.

1. ROW_NUMBER()

To create an additional column in your result set with a rowid based on the Order By clause there is the ROW_NUMBER() function

SELECT ROW_NUMBER() OVER (ORDER BY s.Distance) as rowid,
       s.Name,
       s.Distance
FROM Star s  

2. RANK()

To change the rowid to a ranking just change the ROW_NUMBER() function to RANK()

SELECT RANK() OVER (ORDER BY s.Distance) as ranking,
       s.Name,
       s.Distance
FROM Star s  

3. DENSE_RANK()

RANK() skips id’s when records have duplicate values. In most situations this is just the behaviour you need. If not try the DENSE_RANK() function.

SELECT DENSE_RANK() OVER (ORDER BY s.Distance) as ranking,
       s.Name,
       s.Distance
FROM Star s   

4. NTILE(x)

Another possibility is to divide the results in groups. So let’s say we what to categorise the stars in four groups: very close, close, far and very far. This can be achieved by:

SELECT NTILE(4) OVER (ORDER BY s.Distance) as groups,
       s.Name,
       s.Distance
FROM Star s   

By adding a CASE statement you can easily convert the group number into a description:

SELECT groups = CASE NTILE(4) OVER (ORDER BY s.Distance)
       WHEN 1 THEN 'very close'
       WHEN 2 THEN 'close'
       WHEN 3 THEN 'far'
       WHEN 4 THEN 'very far'
       END,
       s.Name,
       s.Distance
FROM Star s  

have fun!