Join egghead, unlock knowledge.

Want more egghead?

This lesson is for members. Join us? Get access to all 3,000+ tutorials + a community with expert developers around the world.

Unlock This Lesson

Already subscribed? Sign In

Autoplay

    Rank Records with Rank and Dense Rank

    Brett CassetteBrett Cassette

    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.

    postgresPostgreSQL
    Code

    Code

    Become a Member to view code

    You must be a Member to view code

    Access all courses and lessons, track your progress, gain confidence and expertise.

    Become a Member
    and unlock code for this lesson
    Transcript

    Transcript

    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.

    Discuss

    Discuss