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

    Compare the Outliers Using First Value and Last Value

    Brett CassetteBrett Cassette

    How does each runner compare with the first and last place finisher? What about the nth place finisher? First_value, last_value, and nth_value can make these decisions.

    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 What if the rows we want to compare to are not immediately above or below our rows? They're not lagging or leading. We can get the first value in a partition. We can ask what the first place runner got in our race. We can partition by our individual division.

    00:18 This will be first place's pace. We can see how much we lost to first place by. We see in division one, 3.08 is the first place pace. Then, we can also look at the last value, although this will be a little bit different.

    00:37 If we just partition by division, that would be the right thing, but we do want to make sure that we're still ordering by pace, ascending. This throws everything topsy-turvy, because this will only be the rows that have already been processed. We actually just want the last value in the partition.

    01:02 This is where we need to use a row statement again. We need to be rows between unbounded preceding and unbounded following, so that we actually consider the entire frame of reference when we're looking for the last value with this type of ordering.

    01:19 This is our last place pace. Then it's very simple for us to compare how much we won or lost by. The same way we've done before, we'll use a sub-query. That's the query we need.

    01:38 Now we can say, "This is how much we lost to first place by. The first place person did not lose to first place by anything. This is how much they beat last place by." We can see the same thing in our frame down here at the bottom for the loser.

    Discuss

    Discuss