ROW_NUMBER, this is how you emulate it in MySQL

Preamble

​​

In this article, you will learn how to emulate the row_number() function in MySQL. We will show you how to add a sequential integer to each line or group of lines in the result set.

Note that MySQL supports the row_number() window function since version 8.0. If you are using MySQL 8.0 or later, check the ROW_NUMBER() function. Otherwise, you can continue learning how to emulate the ROW_NUMBER() function.

Introduction to the ROW_NUMBER() function

ROW_NUMBER() is a window function that returns an ordinal number for each line starting from 1 for the first line.

Before version 8.0 MySQL did not support ROW_NUMBER() like Microsoft SQL Server, Oracle, or PostgreSQL. Fortunately, MySQL provides session variables that you can use to emulate the ROW_NUMBER() function.

SET@row_number=0;
SELECT
(@row_number:=@row_number + 1) AS num,
firstName,
lastName
FROM
employees
ORDER BY firstName, lastName
LIMIT 5;

In this example:

• First, define the named variable @row_number and set its value to 0. The session variable @row_number specified with the @ prefix.
• Then select the data from the @row_number table and increase the value of the @row_number variable by one for each row. We use the LIMIT offer to limit the number of rows returned to five.

Another method is to use a session variable as a derived table and cross-connect it to 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;

Note that the derived table must have its own alias to make the query syntactically correct

MySQL ROW_NUMBER – add line number to each group

How about the functionality of ROW_NUMBER() OVER(PARITION BY … )? For example, what to do if you want to add a line number to each group and it is reset for each new group.

Let’s take a look at the table from the example database :

SELECT
customerNumber,
paymentDate,
amount
FROM
payments
ORDER BY
customerNumber;

Suppose that for each client you want to add a line number, and the line number is reset every time the client number is changed.

To achieve this, you must use two session variables, one for the line number and the other to store the old client number to compare it with the current one as the next request:

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;

In this example, we use a CASE expression in the query. If the client number remains unchanged, we increase the @row_number variable, otherwise, we reset it to one.

This query uses a derived table and cross-connection to get 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 BYcustomerNumber;

In this article, you learned two ways to emulate the row_number window function in MySQL.

MySql 26 | ROW_NUMBER() Function in MySQL

Enteros

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of RDBMS, NoSQL, and machine learning database platforms.