MySQL ROW_NUMBER() Function

If your interested to learn about the MySQL Partitioning

The ROW_NUMBER() function in MySQL is used to returns the sequential number for each row within its partition. It is a kind of window function. The row number starts from 1 to the number of rows present in the partition. It is to be noted that MySQL does not support the ROW_NUMBER() function before version 8.0, but they provide a session variable that allows us to emulate this function.

Syntax

The following are the basic syntax to use ROW_NUMBER() in MySQL:

ROW_NUMBER() OVER (<partition_definition> <order_definition>)  

How do I use Row_num in SQL?

You can use ROWNUM to limit the number of rows returned by a query, as in this example: SELECT * FROM employees WHERE ROWNUM < 10; If an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause.

1. Partition Definition

Syntax:

<code>partition by &lt;expression1&gt;,&lt;expression2&gt;,….&lt;expression n&gt;</code>

The Partition by clause is used to break the rows or records into smaller chunks of data. The expression followed by the partition clause is a kind of valid conditions which can be used in the group by syntax. The expression can be a single expression or multiple expressions separated by commas depending upon the requirement of the query. We can use it as an optional statement. If you neglect the partition by clause from row_number() function query, then the complete output is taken a partition.

2. Order Definition

Syntax:

order by<expression> [acending|decending],[{,<expression>}..]

The order by clause is mainly used to set the orders or sequence of rows or records.

Let us demonstrate it using an example.

First, we are going to create a table named “Person” using the below statement:

CREATE TABLE Person (  
  Name varchar(45) NOT NULL,  
  Product varchar(45) DEFAULT NULL,  
  Country varchar(25) DEFAULT NULL,  
  Year int NOT NULL  
);  

Next, it is required to add values to this table. Execute the below statement:

INSERT INTO Person(Name, Product, Country, Year)   
VALUES ('Stephen', 'Computer', 'USA', 2015),   
('Joseph', 'Laptop', 'India', 2016),   
('John', 'TV', 'USA', 2016),  
('Donald', 'Laptop', 'England', 2015),  
('Joseph', 'Mobile', 'India', 2015),  
('Peter', 'Mouse', 'England', 2016);  

Next, execute the SELECT statement to display the records:

mysql> SELECT * FROM Person;  

We will get the output, as shown below:

MySQL ROW_NUMBER() Function

Now, we can use the ROW_NUMBER() function to assign a sequence number for each record using the below statement:

SELECT *,   
    ROW_NUMBER() OVER(PARTITION BY Year) AS row_num  
FROM Person;  

It will give the following output:

MySQL ROW_NUMBER() Function

Again, we can use the ROW_NUMBER() function to assign a sequence number for each record within a partition using the below statement:

SELECT *,   
    ROW_NUMBER() OVER(PARTITION BY Year) AS row_num  
FROM Person;  

It will give the output as below where two partitions found based on the year (2015 and 2016).

MySQL ROW_NUMBER() Function

MySQL ROW_NUMBER() Using Session Variable

We can emulate the ROW_NUMBER() function to add a row number in increasing order using the session variable. Execute the below statement that add the row number for each row, which starts from 1:

SET @row_number = 0;   
  
SELECT Name, Product, Year, Country,  
    (@row_number:=@row_number + 1) AS row_num  
FROM Person ORDER BY Country;  

In this statement, we have first specify the session variable @row_number indicated by @prfix and set its value 0. Then, we have selected the data from the table Person and increases the value for variable @row_number by one to each row. After the successful execution of the queries, we will get the output as below:

MySQL ROW_NUMBER() Function

Again, we are going to use a session variable as a table and cross join it with source table using the following statement:

SELECT (@row_number:=@row_number + 1) AS row_num, Name, Country, Year  
FROM Person, (SELECT @row_number:=0) AS temp ORDER BY Year;  

We will get the output as below:

MySQL ROW_NUMBER() Function

Some More Examples

Explain the addition of row number for each row: The following statements return four students from the STUDENT table and add a row number for each row, starting from 1.

Query:

<code>set @row_num=0;
<br>select<br>(@row_num:=@row_num+1) AS serial_num,
<br>first_name,
last_name<br>from<br>STUDENT<br>order by first_name,
last_name</code>

Output:

Mysql ROW_NUMBER() Example 1

Explanation:

  • First, defined a variable with a name  @row_num and set it with an initial value to 0. The @row_num is a session variable that is used to increment the value of each row by 1. It is followed by the @ prefix.
  • Then, with the select clause data from the table STUDENT is selected and each row value is incremented by 1 with @row_num with the variable row_num.
  • Order by clause is used to sort or serialize the order of the records in the result set in ascending (ASC) or descending (DESC) order. By default, it sorts in ascending order.
  • In the last line of the query, we have used the limit clause to constrain or restrict the number of returned rows or records to seven.

Another practice to use a session variable is as a derived table and cross joining it with the main table.  Below is the query to use the session variable as a derived table.

Query:

<code>select<br>(@row_num:=@row_num +1)
 AS num1,first_name,
last_name<br>from<br>STUDENT<br>(select @row_num:=0) AS 
s<br>orderby first _name,
last_name<br>limit 4;
</code><strong>Note</strong>:&nbsp;
 The derived table must have its own alias as in this case is‘s’ for the STUDENT&nbsp;
to make the query syntactically correct.<strong>Explanation:
</strong>
  • In the above query, First, defined a variable with a name  @row_num and set it with an initial value to 0. The @row_num is a session variable that is used to increment the value of each row by 1. It is followed by the @ prefix.
  • We have used the concept of subquery which is a query within a query. One select clause is an outer query and another one is the inner query is declared with variable row_num with initailized value as 0, and the outer query is used to increment the row value of the result set.
  • Then, with the select clause data from the table STUDENT is selected and each row value is incremented by 1 with @row_num with the variable row_num.
  • Order by clause is used to sort or serialize the order of the records in the result set in ascending (ASC) or descending (DESC) order. By default, it sorts in ascending order.
  • In the last line of the query, we have used the limit clause to constrain or restrict the number of returned rows or records to seven.

Giving a row number to each group of data, If we want to add a row number to each group, to explain this, we have taken a bank table from my database.

Query:

<code>select<br>Cust_num,
<br>Amt<br>from<br>Bank<br>orderby<br>Cust_num;
</code>

We want to add a row number or sequence number to each customer, and the row number is also reset while the customer number is being changed.

For this purpose, we have to take two session variables, one for the row number increment andanother for storing the old customer number to compare it with the current customer number.

the below query is the illustration for adding a row numbers to the group of customers.

Query:

<code>set @row_num:=0;
<br>select @row_num:=CASE<br>when @cust_no=Cust_num
<br>then @row_number+1<br>else 1<br>end AS num,
<br>@cust_no:=Cust_num cust_num,
Amt<br>from bank<br>order by Cust_num;
</code>

Output:

Mysql ROW_NUMBER() Example 2

Explanation: In the above query, we have used the CASE expression in the query, the  @row_num variable is incremented, If the customer number remains the same otherwise, It is reset to 1. The above query uses a derived table and the cross join to give the same outcome.

Query:

<code>select<br>@row_num:=CASE<br>
When @cust_no=Cust_num<br>then<br>@row_num+1
<br>else 1<br>end AS num1,
<br>@cust_no:=cust_numcust_num,
Amt<br>from<br>Bank,
<br>(select @cust_no:=0,@row_num:=0) as b<br>order by Cust_num;
</code>

Conclusion

In this article on the row_number function, we have learned how to use the MySQL row_number() function to generate a serial number for each row or record in a result set of data. In this article, we have learned how to use a select statement and order by clause and cross join.

MySQL ROW_NUMBER() Function
Show Buttons
Hide Buttons