Compare the Outliers Using First Value and Last Value

Brett Cassette
InstructorBrett Cassette
Share this video with your friends

Social Share Links

Send Tweet
Published 7 years ago
Updated 5 years ago

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.

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

~ just now

Member comments are a way for members to communicate, interact, and ask questions about a lesson.

The instructor or someone from the community might respond to your question Here are a few basic guidelines to commenting on

Be on-Topic

Comments are for discussing a lesson. If you're having a general issue with the website functionality, please contact us at

Avoid meta-discussion

  • This was great!
  • This was horrible!
  • I didn't like this because it didn't match my skill level.
  • +1 It will likely be deleted as spam.

Code Problems?

Should be accompanied by code! Codesandbox or Stackblitz provide a way to share code and discuss it in context

Details and Context

Vague question? Vague answer. Any details and context you can provide will lure more interesting answers!

Markdown supported.
Become a member to join the discussionEnroll Today