Ranking the row output
These questions check your knowledge of ranking. Ranking can add an ordered number to each of your output rows.
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
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