In this** MariaDB tutorial**, we will study the use of the **MariaDB Row_Number** statement and we will also cover some examples. There are lists of the topic that comes under discussion:

- MariaDB row_number
- MariaDB row_number over partition by
- MariaDB row_number alternative
- MariaDB row_number error
- MariaDB 5.5 row number
- How to show row number in MariaDB

**Contents**show

## MariaDB Row_Number

In MariaDB, the **Row_Number()** function displays no of given rows starting with one followed by ORDER BY clause, and with duplicate values it receives different row numbers.

It just has the same functions as **RANK() **and** DENSE_RANK() **in MariaDB, where duplicate values will receive the same rank for the same result.

The syntax of row_number is given below:

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

The sample example of the **Row_Number()** function is given below:

```
CREATE TABLE USA_highschool(
school_id INT,
First_Name VARCHAR(10),
Course VARCHAR(10),
Marks INT);
INSERT INTO usa_highschool VALUES
(100,'Steve','Maths',60),
(101,'Tony','Maths',60),
(102,'Clint','Maths',70),
(103,'Natasha','Maths',55),
(104,'T Challa','Biology',60),
(105,'Jermey','Biology',70),
(106,'Jennifer','Physics',80),
(107,'Jenny','Physics',63);
SELECT
ROW_NUMBER() OVER (PARTITION BY course ORDER BY marks DESC)
AS row_num,
course, marks, first_name,school_id
FROM usa_highschool ORDER BY course, marks DESC;
```

As you see in the above query, by using the **row_number()** function it has counted duplicate rows in the **course** column under **order by** clause and arranged **marks** and **first_name** column based on **course** column.

It also added the **row_num** column as a new alias name of the base of **row_number()** function on the **course **column.

This is how to use the row_number function in MariaDB.

## MariaDB Row_Number over partition by

In MariaDB, a **PARTITION BY** clause is used to partition rows of a table into groups and it is always used inside the **OVER()** clause. The partition formed by partition by clause is known as **Window.**

The Partition by clause works on Window functions like- RANK(), LEAD(), etc. Note if by mistake the partition by clause is omitted **OVER()** clause then the whole table will be considered as a single partition.

The syntax of the PARTITION BY clause is given below:

```
Window_function ( expression )
Over ( partition by expr [order_clause] [frame_clause] )
```

Consider a new table USA_highschool:

`SELECT * FROM USA_highschool;`

The sample example of partition by clause is given below:

```
SELECT
ROW_NUMBER() OVER (PARTITION BY course ORDER BY marks DESC)
AS row_num,
course, marks, first_name,school_id
FROM usa_highschool ORDER BY course, marks DESC;
```

As you see in the above query, by using the **partition by** clause it has counted duplicate rows in the **course** column and arranged **marks** column by **order by** clause in descending order.

And it took **course** column followed by **marks** and **first_name** column and arranged them in ascending order by using **order by** clause and also with **marks** column in ascending order into a partition in it.

This is how to use the Row_Number over partition by in MariaDB.

Read MariaDB DateTime

## MariaDB Row_Number Alternative

The alternative of **ROW_NUMBER()** in the MariaDB is **RANK()** and **DENSE_RANK() **function. As the definition says, it displays several given rows starting at one followed by order by clause with matching values for the identical result.

There is one small difference between **row_number()** and **rank(), dense_rank()** is that in **row_number()** function it will arrange them by number wise wherein rank() function and also dense_rank() function, it will arrange them in order wise based on a **partition by** clause along with **order by** clause.

The syntax of **RANK()** and **DENSE_RANK()** function is given below:

**RANK() SYNTAX:**

```
RANK() OVER (
[ PARTITION BY partition_expression ]
[ ORDER BY order_list ]
)
```

**DENSE_RANK() SYNTAX:**

```
DENSE_RANK() OVER (
[ PARTITION BY partition_expression ]
[ ORDER BY order_list ]
)
```

The Sample example of **RANK()** in the MariaDB is shown below:

```
SELECT
RANK() OVER (PARTITION BY course ORDER BY marks DESC) AS rank,
course, marks, first_name,school_id
FROM usa_highschool ORDER BY course, marks DESC;
```

As the above query explains by using the **rank()** function, we have partitioned the **course** column by using **partition by** clause and arranged it in descending order the **marks** column by using **order by** clause and covered it as alias name as the **rank** column.

It is done by using the **select** statement to use the **rank()** function and put them in descending order the **course, marks,** and **school_id** column by using **order by** clause.

**DENSE_RANK() SYNTAX:**

```
SELECT
DENSE_RANK() OVER (PARTITION BY course ORDER BY marks DESC) AS
dense_rank,
course, marks, first_name
FROM usa_highschool ORDER BY course, marks DESC;
```

As the above query explains by using the **dense**_**rank()** function, we have partitioned the **course** column by using **partition by** clause and arranged it in descending order the **marks** column by using **order by** clause and covered it as alias name as the **dense_rank** column.

It is done by using the **select** statement to use the **dense_rank()** function and put them in descending order the **course, marks,** and **school_id** column by using **order by** clause.

If we see clearly in the image, the **dense_rank()** function is just arranged by rank-wise in **ascending order** based on **marks** column and partition by** course** column.

Read MariaDB IF Tutorial

## MariaDB Row_Number error

The syntax error or code error will arise only when you try to run in **row_number** function in the lower version of MariaDB like **MariaDB 5.5**. The error can be raised by silly mistake or query mistake or else there is no other way to error to come in **row_number** function.

For the lower version, you need to write the syntax example of the **row_number** function as shown below:

```
SELECT
MyData.*,
@row_num:= @row_num + 1 AS row_num
course,marks,first_name
FROM
usa_highschool,
ORDER BY usa_highschool.`course`,usa_highschool.`marks` DESC;
```

Well, we are using MariaDB 10.5 or 10.6 version for row_number() function. So to write syntax example of row_number function is shown below:

```
SELECT
ROW_NUMBER() OVER (PARTITION BY course ORDER BY marks DESC)
AS row_num,
course, marks, first_name,school_id
FROM usa_highschool ORDER BY course, marks DESC;
```

As we see in the above image, the **row_number()** function worked without getting any error but if any error will rise then we need to work according to the **row_number() function **which is given below:

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

Read MariaDB DATEDIFF Function

## MariaDB 5.5 Row_Number

The latest version of **MariaDB** is **10.7** which was released on **8th November 2021**. The **MariaDB 5.5 **is a lower version or low upgrade version to work on the higher amount of data.

The sample example of MariaDB 5.5 of **row_number** function is shown below:

```
SELECT
MyData.*,
@row_num:= @row_num + 1 AS row_num
course,marks,first_name
FROM
usa_highschool,
ORDER BY usa_highschool.`course`,usa_highschool.`marks` DESC;
```

The upgrade or higher version of MariaDB to use **row_number()** function is given below:

```
SELECT
ROW_NUMBER() OVER (PARTITION BY course ORDER BY marks ASC)
AS row_num,
course, marks, first_name,school_id
FROM usa_highschool ORDER BY course, marks ASC;
```

As we see in the above image, the query has worked with a new syntax method as it used in **MariaDB 10.5/6/7**.

## MariaDB Show Row_Number

In this topic **“MariaDB show row_number”** meant to “use a **select** statement to show row_number function”. The syntax of **row_number()** function is given below:

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

The sample example of the **row_number** function in MariaDB is given below:

```
SELECT
ROW_NUMBER() OVER (PARTITION BY course ORDER BY marks DESC)
AS row_num,
course, marks, first_name,school_id
FROM usa_highschool ORDER BY course, marks DESC;
```

The above is an example of **MariaDB Show Row_Number**.

Related MariaDB tutorials:

- MariaDB Create Sequence
- MariaDB Primary Key
- MariaDB Select Statement
- MariaDB regexp + Examples
- MariaDB Delete Row
- MariaDB Full Outer Join
- MariaDB Cast with Examples
- MariaDB Join with Examples
- MariaDB Comment – How to use

In this tutorial, we have learned about the** MariaDB Row_Number **using some examples. Moreover, we have also covered the following topics in this tutorial as given below:

- MariaDB row_number
- MariaDB row_number over partition by
- MariaDB row_number alternative
- MariaDB row_number error
- MariaDB 5.5 row number
- MariaDB show row number

I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.

Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.