DENSE_RANK

The SQL DENSE_RANK() function is a built-in ranking function in SQL that assigns a unique rank to each row within a result set, based on the values in one or more columns. Unlike the RANK() function, which leaves gaps in the rank sequence when there are ties, the DENSE_RANK() function always produces a consecutive ranking, with no gaps.

Syntax

The syntax of the DENSE_RANK() function is as follows:

DENSE_RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

The function is used in conjunction with the OVER() clause, which defines the window of rows over which the ranking is calculated. The PARTITION BY clause is optional and allows you to divide the result set into groups based on one or more columns. The ORDER BY clause specifies the column or columns used to determine the ranking order.

Example

Here’s an example of how to use the DENSE_RANK() function in a query:

SELECT 
customer_name, sales_amount, 
DENSE_RANK() OVER (ORDER BY sales_amount DESC) as dense_rank
FROM sales;

In this example, we are selecting the customer name and sales amount from the “sales” table, and using the DENSE_RANK() function to assign a dense rank to each row based on the sales amount, in descending order. The result set will include three columns: customer_name, sales_amount, and dense_rank.

The DENSE_RANK() function is useful for ranking items in a result set when you want to ensure that each rank is unique and consecutive, even when there are ties. It is commonly used in financial applications to calculate rankings of stocks or mutual funds based on returns or performance.