MySQL DENSE_RANK

The MySQL DENSE_RANK function is used to assign a unique rank to each distinct row within a result set based on the specified column or columns. Unlike the RANK function, DENSE_RANK does not leave gaps in the ranking when there are ties. If two or more rows have the same values for the ordering columns, they receive the same rank, and the next distinct row receives the next rank without any gaps.

Syntax

Here is the syntax for the DENSE_RANK function in MySQL:

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

PARTITION BY: Optional clause that divides the result set into partitions to which the DENSE_RANK is applied independently. If not specified, the function treats the entire result set as a single partition.

ORDER BY: Specifies the columns by which the ranking is determined. You can order by one or more columns, and each column can be sorted in ascending (ASC) or descending (DESC) order.

Example

Now, let’s look at an example to illustrate the usage of the DENSE_RANK function:

Consider a table named sales with the following data:

+---------+------------+-------+
| OrderID | CustomerID | Sales |
+---------+------------+-------+
|   1     |     101    |  500  |
|   2     |     102    |  700  |
|   3     |     103    |  500  |
|   4     |     101    |  800  |
|   5     |     102    |  900  |
|   6     |     103    |  700  |
+---------+------------+-------+

Now, let’s use the DENSE_RANK function to assign ranks based on the sales amount for each customer:

SELECT
  OrderID,
  CustomerID,
  Sales,
  DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY Sales DESC) AS Rank
FROM
  sales;

The result would be:

+---------+------------+-------+------+
| OrderID | CustomerID | Sales | Rank |
+---------+------------+-------+------+
|   4     |     101    |  800  |  1   |
|   1     |     101    |  500  |  2   |
|   5     |     102    |  900  |  1   |
|   2     |     102    |  700  |  2   |
|   6     |     103    |  700  |  1   |
|   3     |     103    |  500  |  2   |
+---------+------------+-------+------+

In this example, the DENSE_RANK function is used to assign ranks to the rows within each partition (customer) based on the sales amount in descending order. The ranks are assigned without any gaps, even when there are ties in the sales amounts.

Benefits of Using DENSE_RANK

The DENSE_RANK function is useful in situations where you want to rank data without leaving any gaps in the ranking sequence. This is particularly useful for situations such as:

Generating leaderboards: The DENSE_RANK function can be used to generate leaderboards for competitions or events.

Analyzing data trends: The DENSE_RANK function can be used to analyze data trends over time. For example, you could use the DENSE_RANK() function to track the ranking of a particular stock price over the past year.

Conclusion

The MySQL DENSE_RANK function is a versatile window function that can be used to rank data in a variety of ways. It is particularly useful in situations where you want to rank data without leaving any gaps in the ranking sequence.