From the course: Advanced SQL – Window Functions
Unlock the full course today
Join today to access over 22,600 courses taught by industry experts or purchase this course individually.
RANK and DENSE_RANK - SQL Tutorial
From the course: Advanced SQL – Window Functions
RANK and DENSE_RANK
- [Man] Rank assigns each row a number between one and the number of distinct values in a partition. If the values of the sorting expressions within the partition are all unique, the sequence will be monotonically increasing by one, which will be the equivalent of the row number. However, unlike row number, when a partition has tied sorting value expression rows, they are all assigned the same rank. The following value receives its rank disregarding the ties, hence introducing gaps in the sequence. Dense_Rank does a very similar thing, but as his name suggests, it avoids the gaps. These are best explained with an example. Let's revisit the routine checkup report we used two demos ago simplified to exclude species for which there were no checkups. Let's add columns for Rank and Dense_Rank, I will be reusing the over clause. So a window clause is probably an order. The row number generated a unique value for each row even…
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.