How the DENSE_RANK() function works in Mariadb?

The DENSE_RANK() function is a window function that assigns a rank to each row within a partition of a result set. The rank of a row is one plus the number of distinct rank values before it. Unlike the RANK() function, the DENSE_RANK() function does not skip any rank values if there are ties. It is useful for ranking data based on some criteria, such as scores, sales, or performance.

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 DENSE_RANK() function operates on a set of rows that are defined by the OVER clause. The OVER clause consists of two optional sub-clauses: PARTITION BY and ORDER BY.

The PARTITION BY sub-clause divides the result set into partitions, or groups of rows that share the same values of the partition expressions. The DENSE_RANK() function is applied to each partition separately and restarts from one for each partition.

The ORDER BY sub-clause specifies the order of the rows within each partition. The DENSE_RANK() function assigns a rank to each row based on the order of the sort expressions. The rows with the same values of the sort expressions are assigned the same rank. The optional ASC or DESC keywords specify the ascending or descending order of the sort expressions, respectively. The default order is ascending.

Examples

Example 1: Ranking students by their scores

In this example, we use the DENSE_RANK() function to rank the students by their scores in a table called students. The table has the following structure and data:

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  score INT
);

INSERT INTO students VALUES
(1, 'Alice', 90),
(2, 'Bob', 80),
(3, 'Charlie', 85),
(4, 'David', 80),
(5, 'Eve', 95);

The query is:

SELECT id, name, score, DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

The output is:

+------+---------+-------+------+
| id   | name    | score | rank |
+------+---------+-------+------+
|    5 | Eve     |    95 |    1 |
|    1 | Alice   |    90 |    2 |
|    3 | Charlie |    85 |    3 |
|    4 | David   |    80 |    4 |
|    2 | Bob     |    80 |    4 |
+------+---------+-------+------+

The DENSE_RANK() function assigns a rank to each row based on the descending order of the score column. The rows with the same score are assigned the same rank. The rank values are consecutive and do not skip any numbers.

Example 2: Ranking products by their sales in each category

In this example, we use the DENSE_RANK() function to rank the products by their sales in each category in a table called products. The table has the following structure and data:

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  category VARCHAR(50),
  sales DECIMAL(10,2)
);

INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 1200.00),
(2, 'Mouse', 'Electronics', 15.00),
(3, 'Keyboard', 'Electronics', 25.00),
(4, 'Monitor', 'Electronics', 200.00),
(5, 'Book', 'Books', 10.00),
(6, 'Pen', 'Books', 1.00),
(7, 'Notebook', 'Books', 5.00),
(8, 'Magazine', 'Books', 3.00);

The query is:

SELECT id, name, category, sales, DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rank
FROM products;

The output is:

+------+----------+-------------+---------+------+
| id   | name     | category    | sales   | rank |
+------+----------+-------------+---------+------+
|    5 | Book     | Books       |   10.00 |    1 |
|    7 | Notebook | Books       |    5.00 |    2 |
|    8 | Magazine | Books       |    3.00 |    3 |
|    6 | Pen      | Books       |    1.00 |    4 |
|    1 | Laptop   | Electronics | 1200.00 |    1 |
|    4 | Monitor  | Electronics |  200.00 |    2 |
|    3 | Keyboard | Electronics |   25.00 |    3 |
|    2 | Mouse    | Electronics |   15.00 |    4 |
+------+----------+-------------+---------+------+

The DENSE_RANK() function assigns a rank to each row based on the descending order of the sales column within each category. The rows with the same sales are assigned the same rank. The rank values are consecutive and do not skip any numbers. The rank values are reset for each category.

Related Functions

Some of the functions that are related to the DENSE_RANK() function are:

• RANK():This function is similar to the DENSE_RANK() function, but it skips the rank values if there are ties. For example, if two rows have the same rank of 2, the next rank will be 4, not 3.
• ROW_NUMBER():This function assigns a sequential number to each row within a partition of a result set. The order of the rows is determined by the ORDER BY clause. Unlike the DENSE_RANK() function, the ROW_NUMBER() function does not assign the same number to the rows with the same values of the sort expressions. It always returns a unique number for each row.
• NTILE():This function divides the rows within a partition of a result set into a specified number of groups, or buckets, and assigns a bucket number to each row. The buckets are numbered from 1 to the number of buckets. The NTILE() function is useful for dividing data into percentiles, quartiles, or other segments.

Conclusion

The DENSE_RANK() function is a useful function for ranking data based on some criteria, such as scores, sales, or performance. It assigns a rank to each row within a partition of a result set, based on the order of the sort expressions. The rank of a row is one plus the number of distinct rank values before it. The rows with the same values of the sort expressions are assigned the same rank. The rank values are consecutive and do not skip any numbers. The DENSE_RANK() function is compatible with other window functions, such as RANK(), ROW_NUMBER(), and NTILE().