EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL ROW_NUMBER()
 

MySQL ROW_NUMBER()

Priya Pedamkar
Article byPriya Pedamkar

Updated May 15, 2023

MySQL ROW_NUMBER()

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Row_number() feature was included in MySQL version 8.0.

Luckily, MySQL provides us with 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 with 1 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 valid condition that 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 the partition by clause is neglected in the row_number() function query, the entire output is treated as a single 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 MySQL ROW_NUMBER()

Below is the Illustration of MySql row_number(). For the illustration purpose, we have taken the product table from our database:

Adding a row number or sequence number for each row of data, we have to use session Variables in the query to estimate the row number(): function.

Example #1

Explain adding a 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;

Output:

Mysql ROW_NUMBER() Example 1

Explanation:

  • First, define a variable with the name @row_num and set it with an initial value of 0. The @row_num is a session variable incrementing each row’s value by 1. The @ prefix follows it.
  • Then, using the select clause, we select data from the table STUDENT and increment each row value by 1 using the variable @row_num as row_num.
  • The ORDER BY clause sorts or arranges the records in the result set in either 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 for using 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;
Note: In this case, the derived table must have its alias as ‘s’ for the STUDENT to make the query syntactically correct.
Explanation:
  • In the above query, define a variable named @row_num and set it with an initial value of 0. The session variable @row_num increments the value of each row by 1. The @ prefix follows it.
  • We have used the concept of subquery, a query within a query. One select clause represents the outer query, while the other represents the inner query, declared with the variable row_num initialized to 0. The outer query increments the row value of the result set.
  • Then, using the select clause, we select data from the table STUDENT and increment each row value by 1 using the variable @row_num as row_num.
  • The ORDER BY clause is responsible for sorting or arranging 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 data group; if we want to add a row number, we have taken a bank table from my database to explain this.

Query:

select
Cust_num,
Amt
from
Bank
orderby
Cust_num;

We want to add a row number or sequence number to each customer, and we also want to reset the row number when the customer number changes.

For this purpose, we have to take two session variables, one for the row number increment and another 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 number 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:

Mysql ROW_NUMBER() Example 2

Explanation: In the above query, we have used the CASE expression in the query. The @row_num variable increments if the customer number remains the same; otherwise, it resets 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. This article taught us how to use a select statement, order by clause, and cross-join.

Recommended Articles

We hope that this EDUCBA information on “MySQL ROW_NUMBER()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Introduction to MySQL Operators
  2. Top 23 MySQL String functions
  3. MySQL vs SQLite | Top 14 Comparisons
  4. Guide to MySQL Timestamp

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW