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
| rowid |
Name |
Distance |
| 1 |
Proxima Centauri |
4 |
| 2 |
Alpha Centauri A |
4 |
| 3 |
Alpha Centauri B |
4 |
| 4 |
Ster van Barnard |
6 |
| 5 |
Wolf 359 |
8 |
| 6 |
Lalande 21185 |
8 |
| 7 |
Sirius A |
9 |
| 8 |
Sirius B |
9 |
| 9 |
UV Ceti |
9 |
| 10 |
BL Ceti |
9 |
| 11 |
Ross 154 |
10 |
| 12 |
Ross 248 |
10 |
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
| ranking |
Name |
Distance |
| 1 |
Proxima Centauri |
4 |
| 1 |
Alpha Centauri A |
4 |
| 1 |
Alpha Centauri B |
4 |
| 4 |
Ster van Barnard |
6 |
| 5 |
Wolf 359 |
8 |
| 5 |
Lalande 21185 |
8 |
| 7 |
Sirius A |
9 |
| 7 |
Sirius B |
9 |
| 7 |
UV Ceti |
9 |
| 7 |
BL Ceti |
9 |
| 11 |
Ross 154 |
10 |
| 11 |
Ross 248 |
10 |
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
| ranking |
Name |
Distance |
| 1 |
Proxima Centauri |
4 |
| 1 |
Alpha Centauri A |
4 |
| 1 |
Alpha Centauri B |
4 |
| 2 |
Ster van Barnard |
6 |
| 3 |
Wolf 359 |
8 |
| 3 |
Lalande 21185 |
8 |
| 4 |
Sirius A |
9 |
| 4 |
Sirius B |
9 |
| 4 |
UV Ceti |
9 |
| 4 |
BL Ceti |
9 |
| 5 |
Ross 154 |
10 |
| 5 |
Ross 248 |
10 |
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
| groups |
Name |
Distance |
| 1 |
Proxima Centauri |
4 |
| 1 |
Alpha Centauri A |
4 |
| 1 |
Alpha Centauri B |
4 |
| 2 |
Ster van Barnard |
6 |
| 2 |
Wolf 359 |
8 |
| 2 |
Lalande 21185 |
8 |
| 3 |
Sirius A |
9 |
| 3 |
Sirius B |
9 |
| 3 |
UV Ceti |
9 |
| 4 |
BL Ceti |
9 |
| 4 |
Ross 154 |
10 |
| 4 |
Ross 248 |
10 |
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
| groups |
Name |
Distance |
| very close |
Proxima Centauri |
4 |
| very close |
Alpha Centauri A |
4 |
| very close |
Alpha Centauri B |
4 |
| close |
Ster van Barnard |
6 |
| close |
Wolf 359 |
8 |
| close |
Lalande 21185 |
8 |
| far |
Sirius A |
9 |
| far |
Sirius B |
9 |
| far |
UV Ceti |
9 |
| very far |
BL Ceti |
9 |
| very far |
Ross 154 |
10 |
| very far |
Ross 248 |
10 |
have fun!