posted on Thursday, May 25, 2006 10:51 AM
by
marathonsqlguy
RANK and processing Marathon Results
In my presentation on May 11 to the Ohio North SQL Server Special Interest Group (you can download my presentation and demo code from this site) I covered Transact-SQL enhancements in SQL Server 2005. I flew through some of the demos, partially due to time, but also partially because I haven't had a chance to really work with some of the features and I am unfamiliar with them. One such feature was the RANK function, and I used the example from Books Online. The example is not very revealing, so I decided to play a bit on my own.
Last weekend I ran the Fargo Marathon (and for a number of reasons managed to tie my Personal Worst). The event, however, made me think about the data possibilities and the rank function. Here's a sample of the results raw data from the race:
| LastName | FirstName | Sex | Age | Time | DIV | CityStateCountry |
| Poteet | Scott | M | 32 | 03:11:08 | M30-34 | N Las Vegas, NV, USA |
| Poteet | Kristin | F | 29 | 03:34:43 | F25-29 | N Las Vegas, NV, USA |
| Jackson | David | M | 42 | 03:39:25 | M40-44 | W Lafayette, IN, USA |
| Ross | Lynnette | F | 41 | 05:26:54 | F40-44 | Mc Intosh |
| Moen | Brandon | M | 22 | 02:37:44 | M20-24 | Mankato, MN, USA |
| Kennedy | James | M | 41 | 02:59:07 | M40-44 | Eagan, MN, USA |
| Steffens | Monty | M | 47 | 03:02:21 | M45-49 | Sartell, MN, USA |
| Faller | Gerry | M | 40 | 03:03:28 | M40-44 | Maple Grove, MN, USA |
| Welu | Mike | M | 40 | 03:03:53 | M40-44 | Canistota, SD, USA |
| Grindall | Meg | F | 25 | 03:04:45 | F25-29 | Fargo, ND, USA |
I built a query to present the results ranking the finishers by their time within their Division like this:
select LastName, FirstName, Sex, Age, CONVERT(char(8), [Time], 108) as Time, DIV,
RANK() over (PARTITION by DIV order by [TIME]) as Place
FROM dbo.OverallResults
ORDER by DIV, [TIME]
The PARTITION clause tells the RANK function where to draw the lines in the ranking process, so by partitioning by division gives me the age group separation, then ordering by Time within the Division gives me the top finishers within each division. This process used to be quite complicated, and the new RANK function makes it a relatively simple process.
| LastName | FirstName | Sex | Age | Time | DIV | Place |
| Rebenitsch | Sarah | F | 18 | 03:46:09 | F15-19 | 1 |
| Pederson | Elizabeth | F | 19 | 03:55:10 | F15-19 | 2 |
| Stepka | Rachel | F | 19 | 04:15:01 | F15-19 | 3 |
| Mueller | Carrie | F | 22 | 03:15:36 | F20-24 | 1 |
| Botnen | Amy | F | 21 | 03:21:59 | F20-24 | 2 |
| Elseth | Mandy | F | 24 | 03:22:51 | F20-24 | 3 |
| Grindall | Meg | F | 25 | 03:04:45 | F25-29 | 1 |
| Kaspar | Erin | F | 26 | 03:09:46 | F25-29 | 2 |
| Field | Jill | F | 26 | 03:13:03 | F25-29 | 3 |
| Cook | Terri | F | 30 | 03:08:33 | F30-34 | 1 |
| Sawtelle | Mindy | F | 33 | 03:08:34 | F30-34 | 2 |
| Reich | Lisa | F | 31 | 03:27:51 | F30-34 | 3 |
| Blatherwick | Joss | M | 19 | 03:20:57 | M15-19 | 1 |
| Baxter | Cody | M | 19 | 03:27:38 | M15-19 | 2 |
| Nelson | Jacob | M | 18 | 03:33:43 | M15-19 | 3 |
| Moen | Brandon | M | 22 | 02:37:44 | M20-24 | 1 |
| Nielsen | Andy | M | 23 | 02:59:10 | M20-24 | 2 |
| Rogers | Nick | M | 21 | 03:06:58 | M20-24 | 3 |
| Wallin | Chad | M | 26 | 02:31:49 | M25-29 | 1 |
| Miller | Shawn | M | 26 | 02:32:33 | M25-29 | 2 |
| Yurek | Jacob | M | 25 | 02:59:33 | M25-29 | 3 |
| West | Patrick | M | 33 | 02:45:59 | M30-34 | 1 |
| Grafenstein-Kinz | Micah | M | 33 | 02:53:28 | M30-34 | 2 |
| Ziegenfuss | Joe | M | 31 | 02:59:14 | M30-34 | 3 |
| Wells | Mike | M | 39 | 02:56:43 | M35-39 | 1 |
| Robin | Jim | M | 38 | 02:57:15 | M35-39 | 2 |
| Schatkowsky | Craig | M | 37 | 03:02:16 | M35-39 | 3 |
No, you won't find my result in there - it wasn't a good day for me, but if you're trying to quickly do the results for the race management group you can see how the new feature makes life easier.
Allen