MySQL ROW_NUMBER, This is How You Emulate It

 

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;

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:

payments table
SELECT customerNumber, paymentDate, amount FROM payments ORDER BY customerNumber;
mysql row_number payments table

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;
mysql row_number per group

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.

Reactions

Post a Comment

0 Comments