In this tutorial, we will learn about the MySQL ROW_NUMBER() function. It is a very important and helpful function that can be used to solve some complex problems. So, we will understand it with the introduction and syntax. And finally, we will see a few examples of the ROW_NUMBER() function, which will help us better understand the topic.
Also read: MySQL DELETE JOIN Statement – A Complete Guide
Introduction to ROW_NUMBER() Function
The ROW_NUMBER() is a window function in MySQL that is used to return the current row number within its partition. The row number starts from 1 and goes up to the number of partition rows. The ROW_NUMBER() function is often used with the ORDER BY clause to get the deterministic result. Without the ORDER BY clause, row numbering is non-deterministic.
If you didn’t understand anything from the above definition, don’t worry because we will see examples here with the output images. So that you can understand the working of the ROW_NUMBER() function.
MySQL ROW_NUMBER() Syntax
The syntax of the ROW_NUMBER() function is straightforward as shown below.
ROW_NUMBER() OVER (<partition_definition> <order_definition>);Code language: SQL (Structured Query Language) (sql)Where,
- ROW_NUMBER – It is a function that assigns the row number.
- <partition_definition> – It defines how you want the function to assign the row numbers. Such as “PARTITION BY col_name” will make the partition by the given specified column and assign the row numbers accordingly. It is an optional parameter.
- <order_definition> – It defines the order of the data by using the ORDER BY clause.
Now let’s see some examples of the ROW_NUMBER() function.
MySQL ROW_NUMBER() Examples
Before taking the examples, we will use the already created table of the given description. Also, the table contains the dummy data for the operations.
Example 1. Add Row Number To Each Row
MySQL provides us with the ROW_NUMBER function to assign the row numbers. First, we will see the way to assign row numbers using the MySQL ROW_NUMBER function and then we will see the alternate way without the function.
First, we want to assign the numbers to each row irrespective of the repeated data. This means, even if the same customer id is present in the table twice or more, we will assign a unique number to each row.
SELECT *, ROW_NUMBER() OVER(ORDER BY cust_id) as RN FROM customers;Code language: SQL (Structured Query Language) (sql)
Here, we have used the ORDER BY clause to sort the output data by the “cust_id” column. Note that we didn’t use the PARTITION BY clause here. So, we will get unique row numbers in the table as shown in the output image.
Example 2. Add Row Number With Partition
What if you want to assign the unique numbers to only unique rows and the incrementing numbers to the repeated records? For example, if the customer_id 101 has three records in the table, then we want to assign the number 1 to the first record, 2 to the second record and 3 to the third record, even if they are not continuous. If there is any unique record between these records, then that row must get 1 as the row number.
We can achieve this using the PARTITION statement in the ROW_NUMBER Function.
SELECT *, ROW_NUMBER() OVER(PARTITION BY name) as RC FROM customers;Code language: SQL (Structured Query Language) (sql)
Here, we have used the ROW_NUMBER function and created the partition by the “name” column of the table. That way, the unique rows will get the row number 1, and the repeated rows will get the row number as +1 from the previous row count, as shown in the output above.
In the above query, we can use the “cust_id” as well instead of the “name” for the partition.
Example 3. MySQL ROW_NUMBER Using Session Variable.
In the previous example, we assigned the numbers to rows using the ROW_NUMBER function. Now we will learn how to assign the row numbers using the session variable.
It is an easy alternative method to the previous example, and its implementation is straightforward. Let’s see.
SET @row_num=0;
SELECT (@row_num:=@row_num+1) as RC, cust_id, name, occupation, age
FROM customers
ORDER BY age;Code language: SQL (Structured Query Language) (sql)First, we have set the session variable and initialized it to 0.
Second, we increment the value of the session variable by 1 every time the row is fetched from the table.
Third, we have used the ORDER BY clause to sort the data by the customers’ age.
Now let’s see an output.
As you can see, we have received the expected output.
Conclusion
In this tutorial, we learned what the ROW_NUMBER function and its syntax is. We also went through a few examples to understand the working of the ROW_NUMBER function through the output images. There is a wide number of applications of the ROW_NUMBER function.
However, this is the introductory tutorial on the ROW_NUMBER function, and we will create a detailed tutorial on a bunch of practical use cases of it. Till then, practise as much as you can to understand the topic at best.
Reference: https://stackoverflow.com/questions/1895110/row-number-in-mysql