Rank Records with Rank and Dense Rank
If you have a list of competitors in a race, and a list of their finishing times, how do you create a list of what place they came in? Rank and dense rank can help you out; they differ in how they handle ties.
[00:00] Here, I've got a list of runners in a race. They are all in different divisions. Let's rank them, actually, by their division. We will create a partition for each division. We'll just take a look at what this looks like so far.
[00:20] Again, we have them partition by the division, but we don't have them ordered in any specific way. Everyone has the same ranking. They get first place. You need to tell this how to order it. We'll order it by pace. The that'll be ascending.
[00:37] The person who came in first place had the fastest pace. The person who came in second place had the next fastest, etc. We come down here. We look at ties. 9.12. These guys both get 20th place. That makes sense. This is different from ROW_NUMBER.
[00:59] We wouldn't want to use ROW_NUMBER here because then, the first person who is arbitrarily ordered gets 20th place and then the next gets 21st place. We do want a ranking here. Then when we take a look at the next person, Liz comes in 22nd place instead of 21st place.
[01:17] That could be how we want to do it if we want to base this solely on the number of competitors. If we want her to be in 21st place, we can use DENSE_RANK. Now, the tie gets 20th. The next position is 21st, 22nd, which continued counting as normal.