Number or Rank Rows
You can use any of these built-in Business Analytics analytic window functions to rank or number rows in partitions:
row_number()rank() dense_rank() To support ranking you must include an Order By clause within the partition definition. The first column in the internal Order By clause is used to determine row or ranking order.
You also cannot apply numering or ranking to windows within the partition.
Row_number, Rank and Dense_rank Example
This example uses all of these built-in ranking/numbering functions with the same partition (legislators within each chamber of the US congress) and sort order (ordered by state) to illustrate the different effects of each function:
row_number() provides a simple sequential number for each row based on its position within the sorted partition. Note that this example is sorted, but that is not required for simple numbering.
rank() assigns the same index number to all rows that match one unique value for the first sort column in the Order By expression. Rows are sorted within the partition based on the full Order By expression. The actual rank numbers are
not contiguous, however, skipping unused numbers to reflect the number of rows within that rank.
So in this example, the row number for the first legislator for Alabama is 2 and his rank is also 2. Rank for all the remaining legislators for Alabama remains at 2 although their row numbers continue to increment. When the first row for Arkansas is found, the row number increments to 9 and so does the rank, skipping ranks of 3-8.
dense_rank() also assigns the same index number to all rows that match one unique value for the first sort column in the Order By expression. Unlike
rank(), however,
dense_rank() results
are contiguous.
So in this example, the rank number for the first legislator from Arkansas is 9, matching his row number. The dense rank number is 3, being the next available ranking number.
