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  ROW_NUMBER() functions 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 numbers 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;

In this example:

• First, define a variable named  @row_number and set its value to 0. The @row_number is a session variable indicated by the @ prefix.
• Then, select data from the table employees and increase the value of the  @row_number variable by one for each row. We use the LIMIT clause to constrain the 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;

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;

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;

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;

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