Ranking the row output
These questions check your knowledge of ranking. Ranking can add an ordered number to each of your output rows.
The commands used are: rank, row_number, dense_rank, cume_dist, percent_rank,
Read on to Learn about Ranking
Row_number, Rank and Dense_rank
These three functions will number each row. Using row_number gives a result that must always be unique. Each row is assigned a different value even if they are equal
The easiest way to explain rank and dense_rank is to imagine ranking the runners of a race. Consider: If 2 runners finish in equal 3rd, is the next runner's place 4th (dense_rank) or 5th (rank).
select name, time, row_number() over (order by time), rank() over (order by time), dense_rank() over (order by time) FROM runners order by time
name | time | row_number | rank | dense_rank |
---|---|---|---|---|
andy | 101 | 1 | 1 | 1 |
bob | 103 | 2 | 2 | 2 |
cedric | 104 | 3 | 3 | 3 |
dave | 104 | 4 | 3 | 3 |
eric | 108 | 5 | 5 | 4 |
Cume_dist & Percent_rank
These 2 functions calculate the relative rank of a group of rows
- percent_rank returns a number from 1 to 0. The highest being 1 and the lowest 0.
- cume_dist will return a number from 1 towards 0 but never 0. Think of it this way: If there are 4 different values do you count down from 1 in steps of 0.25 (percent_rank) or in steps of 0.2 ensuring that we never hit 0 (cume_dist)
select name, time, percent_rank() over (order by time), cume_dist() over (order by time) FROM runners order by time
name | time | percent_rank | cume_dist |
---|---|---|---|
andy | 101 | 0 | 0.2 |
bob | 103 | 0.25 | 0.4 |
cedric | 104 | 0.5 | 0.8 |
dave | 104 | 0.5 | 0.8 |
eric | 108 | 1 | 1 |