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
 
nametimerow_numberrankdense_rank
andy101111
bob103222
cedric104333
dave104433
eric108554

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
nametimepercent_rankcume_dist
andy10100.2
bob1030.250.4
cedric1040.50.8
dave1040.50.8
eric10811