Determining the performance of a particular business entity compared to its peers is central to a wide variety of business decisions. Examples include:
Identifying assets with the highest utilization.
Determining the worst-selling products by region.
Finding the best-performing salespeople.
Prior to the release of Oracle8i, programmers could use the ORDER BY clause to sort a result set on one or more columns, but any further processing to calculate rankings or percentiles had to be performed using a procedural language. Beginning with Oracle8i, however, developers can take advantage of several new functions to either generate rankings for each row in a result set or to group rows into buckets for percentile calculations.
RANK, DENSE_RANK, and ROW_NUMBER
The RANK, DENSE_RANK, and ROW_NUMBER functions generate an integer value from 1 to N for each row, where N is less than or equal to the number of rows in the result set. The differences in the values returned by these functions revolves around how each one handles ties:
ROW_NUMBER returns a unique number for each row starting with 1. For rows that have duplicate values, numbers are arbitrarily assigned.
DENSE_RANK assigns a unique number for each row starting with 1, except for rows that have duplicate values, in which case the same ranking is assigned.
RANK assigns a unique number for each row starting with 1, except for rows that have duplicate values, in which case the same ranking is assigned and a gap ...