MariaDB Row_Number Tutorial

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

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.

Read MariaDB Date Function

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.

Read MariaDB AUTO_INCREMENT

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