Ranking window functions
1. Ranking window functions
Let's take those window function skills a level deeper; it's time to do some ranking!2. RANK()
The `RANK` function is used to assign a ranking to records based on some field. `RANK` should feel quite similar to `ROW_NUMBER`; the syntax is almost identical. One, big difference, however, is how `RANK` handles ties; we'll dig into that later. An `ORDER BY` must be included in `OVER` when using `RANK`. Here, we're ordering our result set by `km_traveled`. This allows us to rank the concert-go'ers in our result set by how far they traveled to the venue.3. RANK()
Our new column, `closest_attendees`, ranks attendees based on how close they were to the concert venue. Take a look at the third and forth rows; the `km_traveled` for these records is the same. As a result, their ranking is the same. The fifth record picks up at five, rather than four. Here, `RANK` is handling ties; something that `ROW_NUMBER` does not do.4. RANK() with DESC
By default, `ORDER BY` sequences records in ascending order. If we were instead interested in ranking attendees who traveled the furthest at the top, we could use `DESC` in the `ORDER BY`.5. RANK() with DESC
We've flipped the ranking! Now, the attendees who traveled the furthest have a high ranking, with the closest attendees ranking beneath them.6. FIRST_VALUE and LAST_VALUE
What if we're only interested in the highest and lowest ranked records in a column? We have a tool for that! `FIRST_VALUE` and `LAST_VALUE` return the first-ranked and last-ranked records in a result set, respectively. This makes it easy to compare records in a column to it's extremes. Both `FIRST_VALUE` and `LAST_VALUE` take the name of a field, and must include an `ORDER BY`. The `ORDER BY` determines the ranking of the records. Then, the first or last value from the specified field will be output. While the column passed to `FIRST_VALUE` or `LAST_VALUE` and the field in the `ORDER BY` don't need to match, they typically do.7. The Good and the Bad
Here, we've defined `most_satisfied` using `FIRST_VALUE` to store the first-ranked `satisfaction_score`. Since we ordered by `satisfaction_score` in descending order, the result will be the largest value. This means that `least_satisfied` should capture the last-ranked, or smallest, `satisfaction_score`.8. The Good and the Bad
The highest-ranked `satisfaction_score` happens to be 98, which is shown in `most_satisfied`. `least_satisfied` captures the lowest-ranked value, in this case, 4. This helps to put `satisfaction_score` into context for each record. Without `FIRST` and `LAST_VALUE`, we'd need to use a common table expression or subquery to find the minimum and maximum of the `satisfaction_score` column before joining it to the result set. `FIRST_VALUE` and `LAST_VALUE` make this much easier!9. Let's practice!
Your turn! It's time to put your new skills to work with a couple of exercises!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.