Compare the Outliers Using First Value and Last Value

[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.