Ranking Inside a Partition

In previous examples, we have witnessed the importance of accessing the first and last rows within a partition. Although the partition ordering already implies a definition of the first and last row, explicitly ranking rows inside a partition gives us more fine-grained control. This applies to peers resulting from ties between values concerning the partition order, in particular. Here, peers refer to rows that are equal in value subject to the partition order. To handle this case, MySQL offers three window functions that rank the rows within a partition, namely RANK(), DENSE_RANK(), and PERCENT_RANK().

Absolute ranks with gaps

The RANK() is a function without arguments that assigns each row its rank within the partition where peers receive the same rank. Groups of adjacent peers concerning the partition order do not receive consecutive ranks. That is, a group of $n$ peers advance the rank $r$ by $n$, while all peers in that group receive the same rank. The adjacent group of peers continues with a rank of $n + r$. Without any partition order, the window function returns the same rank for all rows (i.e., all rows are considered to be peers). With the possibilities of RANK(), we can now solve one of our previous problems, receiving the most powerful car model by type, more efficiently: