Window functions: RANK and DENSE RANK

SQL provides two functions, RANK and DENSE RANK, that allow you to assign unique rankings to rows in a query result set. These functions can be useful for a variety of purposes, such as determining the relative position of rows based on one or more columns, or for breaking ties between rows with the same values.

RANK

The RANK function assigns a unique rank to each row in a query result set, based on the values of one or more columns. The basic syntax for the RANK function is as follows:

SQL
RANK() OVER ( [PARTITION BY partition_expression] [ORDER BY order_expression] )

The PARTITION BY and ORDER BY clauses within the OVER clause determine how the rows are divided into partitions and ordered for processing by the RANK function.

For example, if you have a table of sales data and want to rank the salespeople based on their total sales, you could use the following query:

SQL
SELECT salesperson, total_sales, RANK() OVER (ORDER BY total_sales DESC) AS sales_rank
FROM sales_data;

In this query, the salespeople are ranked based on their total_sales values, in descending order.

Note that the RANK function may assign the same rank to multiple rows if they have the same values for the columns being ranked. This is known as a "tie".

DENSE RANK

The DENSE RANK function is similar to the RANK function, but it does not skip any rank values for ties. In other words, if two rows have the same values for the columns being ranked, they will both be assigned the same rank, but the next row will receive the next rank value, rather than skipping a rank value as the RANK function would.

The basic syntax for the DENSE RANK function is as follows:

SQL
DENSE_RANK() OVER ( [PARTITION BY partition_expression] [ORDER BY order_expression] )

For example, the following query assigns dense ranks to the salespeople based on their total_sales values:

SQL
SELECT salesperson, total_sales, DENSE_RANK() OVER (ORDER BY total_sales DESC) AS sales_dense_rank
FROM sales_data;

In this query, even if two salespeople have the same total_sales values, they will both be assigned the same dense rank, and the next salesperson will receive the next rank value.

In conclusion, these window functions allow you to assign unique rankings to rows in a query result set, and they can be useful for a variety of purposes. Whether you need to determine the relative position of rows based on one or more columns, or break ties between rows with the same values, RANK and DENSE RANK are valuable tools to have in your SQL toolkit.

Try it yourself

Using our example orders table and the RANK function, write a query that returns a list of orders ranked by total price. Then try using the DENSE RANK function. Is there a difference? Why or why not?

SQL
SELECT 
    orders.*,
    SUM(quantity * price) AS total_price,
    RANK() OVER (ORDER BY SUM(quantity * price) DESC) AS rank
FROM 
    orders
GROUP BY 
    id, customer_id

SQL
SELECT 
    orders.*,
    SUM(quantity * price) AS total_price,
    DENSE_RANK() OVER (ORDER BY SUM(quantity * price) DESC) AS rank
FROM 
    orders
GROUP BY 
    id, customer_id