MySQL ROW_NUMBER, This is How You Emulate It
Summary: in this tutorial, you will learn how to emulate the row_number() function in MySQL. We will show you how to add a sequential integer to each row or group of rows in the result set.
Notice that MySQL has supported the ROW_NUMBER() since version 8.0. If you use MySQL 8.0 or later, check it out ROW_NUMBER() function. Otherwise, you can continue with the tutorial to learn how to emulate the ROW_NUMBER() function.
Introduction to the ROW_NUMBER() function
The ROW_NUMBER() is a window function that returns a sequential number for each row, starting from 1 for the first row.
Before version 8.0, MySQL did not support the ROW_NUMBER() function like Microsoft SQL Server, Oracle, or PostgreSQL. Fortunately, MySQL provides session variables that you can use to emulate the ROW_NUMBER() function.
MySQL ROW_NUMBER – adding a row number for each row
To emulate the ROW_NUMBER() function, you have to use session variables in the query.
The following statements return five employees from the employees table and add row number for each row, starting from 1.
SET @row_number = 0;
SELECT
(@row_number:=@row_number + 1) AS num,
firstName,
lastName
FROM
employees
ORDER BY firstName, lastName
LIMIT 5;Code language: SQL (Structured Query Language) (sql)
In this example:
- First, define a variable named
@row_numberand set its value to 0. The@row_numberis a session variable indicated by the@prefix. - Then, select data from the table
employeesand increase the value of the@row_numbervariable by one for each row. We use theLIMITclause to constrain a number of returned rows to five.
Another technique is to use a session variable as a derived table and cross join it with the main table. See the following query:
SELECT
(@row_number:=@row_number + 1) AS num,
firstName,
lastName
FROM
employees,
(SELECT @row_number:=0) AS t
ORDER BY
firstName,
lastName
LIMIT 5;Code language: SQL (Structured Query Language) (sql)
Notice that the derived table must have its own alias to make the query syntactically correct.
MySQL ROW_NUMBER – adding a row number to each group
How about the ROW_NUMBER() OVER(PARITION BY ... ) functionality? For example, what if you want to add a row number to each group, and it is reset for every new group.
Let’s take a look at the payments table from the sample database:
SELECT
customerNumber,
paymentDate,
amount
FROM
payments
ORDER BY
customerNumber;Code language: SQL (Structured Query Language) (sql)
Suppose for each customer, you want to add a row number, and the row number is reset whenever the customer number changes.
To achieve this, you have to use two session variables, one for the row number and the other for storing the old customer number to compare it with the current one as the following query:
set @row_number := 0;SELECT
@row_number:=CASE
WHEN @customer_no = customerNumber
THEN @row_number + 1
ELSE 1
END AS num,
@customer_no:=customerNumber customerNumber,
paymentDate,
amount
FROM
payments
ORDER BY customerNumber;
Code language: SQL (Structured Query Language) (sql)
In this example, we use the CASE expression in the query. If the customer number remains the same, we increase the @row_number variable, otherwise, we reset it to one.
This query uses a derived table and the cross join to produce the same result.
SELECT
@row_number:=CASE
WHEN @customer_no = customerNumber
THEN
@row_number + 1
ELSE
1
END AS num,
@customer_no:=customerNumber CustomerNumber,
paymentDate,
amount
FROM
payments,
(SELECT @customer_no:=0,@row_number:=0) as t
ORDER BY
customerNumber;Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned two ways to emulate the row_number window function in MySQL.