# 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 |