How To Use The DENSE_RANK () Function on MySQL

DENSE_RANK () is a window function that assigns the rank of each row within a section or result set without spaces in the ranking of values.The row rank is increased by one from among the different rank values ​​that go before the row. The syntax for the DENSE_RANK () function is as follows:

DENSE_RANK() OVER (
PARTITION BY <expression>[{,<expression>...}]
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)

Here is needs to specify some clarifications:

The PARTITION BY clause divides the result sets created by this FROM clause into sections. The DENSE_RANK () function is applied to each section

The ORDER BY clause determines the order of the rows in each section in which the DENSE_RANK () function works

Unlike the RANK () function, the DENSE_RANK () function always returns consecutive rank values.

Suppose we have a table a with some data examples:

CREATE TABLE а (
val INT
);

INSERT INTO а(val)
VALUES(1),(2),(2),(3),(4),(4),(5);

SELECT
*
FROM
а;

The next statement uses the DENSE_RANK () function to rank each row:

SELECT
val,
DENSE_RANK() OVER (
ORDER BY val
) my_rank
FROM
a;

DENSE_RANK () function example in MySQL

For demonstration, the sales table created in the window function guide will be used. The following statement uses the DENSE_RANK () function to rank sales employees by sales amount.

SELECT
sales_employee,
fiscal_year,
sale,
DENSE_RANK() OVER (PARTITION BY
fiscal_year
ORDER BY
sale DESC
) sales_rank
FROM
sales;

Explanations will be given below:

  • The PARTITION BY clause divides the result sets into sections using the fiscal year
  • The ORDER BY clause indicates the sales order of the sales staff in descending order.
  • The DENSE_RANK () function is applied to each section with the row order specified in the ORDER BY clause.

Now’s Done! Yoy know how to use the  DENSE_RANK() MySQL!

Was this article helpful?

Related Articles

2 Comments

  1. I am glad to express that I’ve an incredibly excellent uncanny feeling I came upon just
    what I needed. I most for sure will makee sure to don?t overlook this web
    site and gove it a look regularly.

Leave A Comment?