# ROW\_NUMBER

## Syntax

```sql
ROW_NUMBER() OVER (
  [ PARTITION BY partition_expression ]
  [ ORDER BY order_list ]
)
```

## Description

`ROW_NUMBER()` is a [window function](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions) that displays the number of a given row, starting at one and following the [ORDER BY](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by) sequence of the window function, with identical values receiving different row numbers. It is similar to the [RANK()](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/rank) and [DENSE\_RANK()](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/dense_rank) functions except that in that function, identical values will receive the same rank for each result.

## Examples

The distinction between [DENSE\_RANK()](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/dense_rank), [RANK()](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/rank) and ROW\_NUMBER():

```sql
CREATE TABLE student(course VARCHAR(10), mark int, name varchar(10));

INSERT INTO student VALUES 
  ('Maths', 60, 'Thulile'),
  ('Maths', 60, 'Pritha'),
  ('Maths', 70, 'Voitto'),
  ('Maths', 55, 'Chun'),
  ('Biology', 60, 'Bilal'),
   ('Biology', 70, 'Roger');

SELECT 
  RANK() OVER (PARTITION BY course ORDER BY mark DESC) AS rank, 
  DENSE_RANK() OVER (PARTITION BY course ORDER BY mark DESC) AS dense_rank, 
  ROW_NUMBER() OVER (PARTITION BY course ORDER BY mark DESC) AS row_num, 
  course, mark, name 
FROM student ORDER BY course, mark DESC;
+------+------------+---------+---------+------+---------+
| rank | dense_rank | row_num | course  | mark | name    |
+------+------------+---------+---------+------+---------+
|    1 |          1 |       1 | Biology |   70 | Roger   |
|    2 |          2 |       2 | Biology |   60 | Bilal   |
|    1 |          1 |       1 | Maths   |   70 | Voitto  |
|    2 |          2 |       2 | Maths   |   60 | Thulile |
|    2 |          2 |       3 | Maths   |   60 | Pritha  |
|    4 |          3 |       4 | Maths   |   55 | Chun    |
+------+------------+---------+---------+------+---------+
```

## See Also

* [RANK()](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/rank)
* [DENSE\_RANK()](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/dense_rank)
* [ORDER BY](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}