What is MySQL ROW_NUMBER() Function?
Mysql ROW_NUMBER() function is a type of function that returns a number for each row in sequence or serial, beginning from 1 for the first record of the result set to the end in ascending order. It assigns a number value to each row or record in the table from 1 given to the first row to n to the nth row.
Feature of row_number() was included Mysql version 8.0
Luckily, MySQL provides us session variables by which we can find the row_number() function.
More precisely, It returns the serial number of a row within a partition of a result set or table, beginning with1 for the first row in each partition till n for the nth row.
Syntax of MySQL ROW_NUMBER()
row_number() over (<partition><order by>)
1. Partition Definition
Syntax:
partition by <expression1>,<expression2>,….<expression n>
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.
Examples to Implement of MySQL ROW_NUMBER()
Below is the Illustration of MySql row_number(). For the illustration purpose we have taken product table from our database:
Adding a row number or sequence number for each row of data, to estimate the row number(): function, we have to use session Variables in the query.
Example #1
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:
set @row_num=0;
select
(@row_num:=@row_num+1) AS serial_num,
first_name,last_name
from
STUDENT
order by first_name,last_name
limit 4;
4.5 (5,030 ratings)
View Course
Output:
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.
Example #2
Query:
select
(@row_num:=@row_num +1) AS num1,first_name,last_name
from
STUDENT
(select @row_num:=0) AS s
orderby first _name,last_name
limit 4;
- 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.
Example #3
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:
select
Cust_num,
Amt
from
Bank
orderby
Cust_num;
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.
Example #4
The below query is the illustration for adding a row numbers to the group of customers.
Query:
set @row_num:=0;
select @row_num:=CASE
when @cust_no=Cust_num
then @row_number+1
else 1
end AS num,
@cust_no:=Cust_num cust_num,Amt
from bank
order by Cust_num;
Output:
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.
Example #5
Query:
select
@row_num:=CASE
When @cust_no=Cust_num
then
@row_num+1
else 1
end AS num1,
@cust_no:=cust_numcust_num,Amt
from
Bank,
(select @cust_no:=0,@row_num:=0) as b
order by Cust_num;
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.
Recommended Articles
This is a guide to MySQL ROW_NUMBER(). Here we discuss the Introduction to MySQL ROW_NUMBER() Function and the practical examples and different subquery expressions. You can also go through our suggested articles to learn more –
