# 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 `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 |

2 | Bob | 80 | 4 |

4 | David | 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 |
---|---|---|---|---|

1 | Laptop | Electronics | 1200.00 | 1 |

4 | Monitor | Electronics | 200.00 | 2 |

3 | Keyboard | Electronics | 25.00 | 3 |

2 | Mouse | Electronics | 15.00 | 4 |

5 | Book | Books | 10.00 | 1 |

7 | Notebook | Books | 5.00 | 2 |

8 | Magazine | Books | 3.00 | 3 |

6 | Pen | Books | 1.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()`

function: 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()`

function: 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()`

function: 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()`

.