RANKING FUNCTIONS

The popularity of window functions is only rivaled by their usefulness. This is especially true for the subset of window functions commonly referred to as ranking functions. I’ll first introduce the why behind ranking functions and then dive into the how and when. The how walks through calculating rank using SQL to meet a common reporting requirement while the when explores scenarios for each specific ranking function. At the end of this page, I’ll briefly comment on why it is important to provide proper context to your audience around ranking outcomes. The schemas, corresponding tables, and data used to support the examples on this page can be found at livesql.oracle.com.

Why Ranking Functions?
A number by itself is arguably meaningless, so we give life to numbers by providing context. We have a propensity for using numbers to describe things in terms of relative size, importance, and position. To illustrate, consider the following statements regarding the data displayed in the table below: sales representative 161 is the number one sales representative, the top three performers, located in rows two through four, are sales representatives 161, 156, and 154, and the bottom three performers, located in rows seven through nine, are 163, 153, and 160.

Associating each sales representative’s performance with an integer value is easy, natural, and at the same time, powerful. By doing so, we can quickly convey how great or poor each sales representative’s performance is relative to other sales representatives’ performances. This is the concept of rank, and it is usually done based on some specification or set of instructions. For clarity, see the augmented table below where the rank value associated with each sales representative is based on the corresponding total sales value. The largest total sales value has a corresponding rank of 1, second largest total sales value a rank value of 2, and so on.

The integer value we associate with each performance can also be used to convey position or location. For example, we can refer to an observation simply by the row number. Stating, “the performance of sales representative 163 can be found in row 8” allows the consumer of the data to quickly navigate their eyes to this location within the data.

Calculating Rank Using SQL
Modern relational database management systems (RDBMS) allow us to incorporate the ideas of rank and location using window or analytic functions. The specific window functions used to do so are RANK, DENSE_RANK, and ROW_NUMBER. These window functions are shared across most flavors of SQL and are somewhat straightforward to use after a brief introduction on the syntax and clauses. If you’re unfamiliar with the syntax and individual clauses for window functions, then consider reading my brief explanation here before continuing further.

The ranking functions have significant overlap in terms of the SQL code using to implement them; however, the outcome when used against the same data can differ significantly. Let’s walk through a concrete example to better understand the similarity and differences across ranking functions.

Suppose we are given the task of generating a report containing the product ID, product name, category ID, and list price for each product. In addition to these attributes, we are also required to rank each of the products, in descending order, based on their list prices. This is where this seemingly simple task can result in generating an erroneous report. In the absence of details concerning the treatment of ties (i.e., multiple products with the same list price), any of the three ranking functions could be used. Let’s dive into this further.

In the result set below, notice the integer values returned by each function, ROW_NUMBER, RANK, and DENSE_RANK are identical up until the 13th product listed. The difference observed in rows 12 and 13 is attributable to the products having the same list price value of 999. The ROW_NUMBER function assigns consecutive integer values without addressing ties while RANK and DENSE_RANK functions do address ties. RANK assigns the same integer value to the rows with the same list price value and determines the next row’s integer value (i.e., rank) by adding the number of tied rows to the previous or tied rank value. In this case, there are only two rows with the same list price value associated with rank 12, so the rank value assigned to the row following the tie is 14 (i.e., 12 + 2). DENSE_RANK also addresses ties by assigning the tied rows the same rank value; however, the function doesn’t incorporate the same gaps in rank values. Instead, DENSE_RANK assigns the row immediately following a tie the subsequent rank value. Notice, the sequence is 12, 12, 13 where the RANK function assigned the integer values, 12, 12, 14 for the same rows.

SELECT
	oe.product_information.product_id,
    oe.product_information.product_name,
    oe.product_information.category_id,
    oe.product_information.list_price,
    ROW_NUMBER() OVER (
        ORDER BY oe.product_information.list_price DESC
    ) AS row_number,
    RANK() OVER (
        ORDER BY oe.product_information.list_price DESC
    ) AS rank,
    DENSE_RANK() OVER (
        ORDER BY oe.product_information.list_price DESC
    ) AS dense_rank
FROM
	oe.product_information
WHERE
    oe.product_information.list_price IS NOT NULL
ORDER BY
    list_price DESC;

Function Choice
The result set and corresponding SQL code above highlight, at a high level, the similarities and differences among the ranking functions; however, when solving real world problems, you will need to know more than the overlap in syntax and the differences in the rank calculations. You will need to choose the right function for the task at hand.

Is the function being used to assign integer values to each row for reference purposes (e.g., “The sale in question is in row 23.”) or to implement a ranking system (e.g., sales representatives ranked by total sales, descending)? If it is the former of the two, then use the ROW_NUMBER function. If it is the latter of the two, then a conscious choice between the use of RANK and DENSE_RANK is required.

When choosing between the two functions, it is important to consider the impact ties have on the intended message and the overall consequences. For example, imagine annual bonuses at a company are rewarded to product owners with products ranked within the top 5 in terms of distinct purchases. Given the data below, six products are in the top 5 when RANK is used while eight are in the top 5 when DENSE_RANK is used. Choosing one method over another will result in more or less product owners being rewarded an annual bonus.

SELECT
	oe.product_information.product_id,
    oe.product_information.product_name,
    COUNT(DISTINCT oe.order_items.order_id) AS distinct_purchases,
    RANK() OVER (
        ORDER BY COUNT(DISTINCT oe.order_items.order_id) DESC
    ) AS rank,
    DENSE_RANK() OVER (
        ORDER BY COUNT(DISTINCT oe.order_items.order_id) DESC
    ) AS dense_rank
FROM
    oe.order_items
    INNER JOIN
	oe.product_information
    	ON oe.order_items.product_id = oe.product_information.product_id
GROUP BY
    oe.product_information.product_id,
    oe.product_information.product_name
ORDER BY
    distinct_purchases DESC;

If desired, each ranking function’s rules could be expanded to account for those instances where ties occur. For example, in the instances where multiple products have the same distinct purchases count, the total sales generated by each of the products could be used to resolve the ties. Given the data type of total sales, this will largely result in the functions assigning the same integer value to each row. In fact, there is only one tie, and it is inconsequential to our scenario here. See the result set and corresponding SQL code below.

SELECT
	oe.product_information.product_id,
    oe.product_information.product_name,
    COUNT(DISTINCT oe.order_items.order_id) AS distinct_purchases,
    SUM(oe.order_items.unit_price * oe.order_items.quantity) AS total_sales,
    RANK() OVER (
        ORDER BY
    		COUNT(DISTINCT oe.order_items.order_id) DESC,
    		SUM(oe.order_items.unit_price * oe.order_items.quantity) DESC
    ) AS rank,
    DENSE_RANK() OVER (
        ORDER BY
    		COUNT(DISTINCT oe.order_items.order_id) DESC,
    		SUM(oe.order_items.unit_price * oe.order_items.quantity) DESC
    ) AS dense_rank
FROM
    oe.order_items
    INNER JOIN
	oe.product_information
    	ON oe.order_items.product_id = oe.product_information.product_id
GROUP BY
    oe.product_information.product_id,
    oe.product_information.product_name
ORDER BY
    distinct_purchases DESC, total_sales DESC;

Provide the Context
Regardless of which ranking function you use, it is critical your audience understands the ranking system’s specification (i.e., how it works). The audience should be able to quickly discern the significance or insignificance of a particular row’s value. Additionally, messages surrounding ranking outcomes should contain supporting information, if necessary. For example, it would be misleading to state a sales representative’s performance, based on total sales, is in the top 10 when there are only 10 sales representatives or there are 10 other sales representatives with the same total sales value. Be transparent.

Wrap Up
In summary, ranking functions provide us a powerful way to quickly convey relative size, importance, and position within the context of our data. ROW_NUMBER is useful for assigning values to each row based on their position within the data while RANK and DENSE_RANK allow us to implement ranking systems based on specific instructions. When using ranking functions, it is important to provide all the necessary context and information to data consumers.

Knowledge Check
If you’d like to confirm your understanding on the use of ranking functions and other fundamental concepts, consider solving a handful of my practice problems here.