MySQL – Generating Row Number for Each Row Using Variable

MySQL does not have any system function like SQL Server’s row_number () to generate the row number for each row. However, it can be generated using the variable in the SELECT statement. Let us see how MySQL Generating Row Number.

The following table has five rows.

CREATE TABLE mysql_testing(db_names VARCHAR(100));
INSERT INTO mysql_testing
SELECT 'SQL Server' UNION ALL
SELECT 'MySQL' UNION ALL
SELECT 'Oracle' UNION ALL
SELECT 'MongoDB' UNION ALL
SELECT 'PostGreSQL';

Now you can try generating row number values using a variable in two methods

Method 1: Generating Row Number With a Variable in a SELECT Statement

SET @row_number:=0;
SELECT @row_number:=@row_number+1 AS row_number,db_names FROM mysql_testing
ORDER BY db_names;

Method 2 : Use a variable as a table and cross join it with the source table

SELECT @row_number:=@row_number+1 AS row_number,db_names FROM mysql_testing,
(SELECT @row_number:=0) AS t
ORDER BY db_names;

Both the above methods return the following result

row_number db_names
 1 MongoDB
 2 MySQL
 3 Oracle
 4 PostGreSQL
 5 SQL Server

Well, this is a very interesting scenario for MySQL. I would like to know from you if you are aware of such business case situation where you implemented this logic. Looking forward to your comment.

Generating Row Number in MySQL 8.0 and Later

Good news for MySQL users. MySQL 8.0 added window functions, including ROW_NUMBER(), so you no longer need the variable trick. The query now reads just like it does in SQL Server. If you are not sure which version you run, SELECT VERSION(); tells you in a second. On MySQL 5.7 or older, stay with the variable methods above.

SELECT ROW_NUMBER() OVER (ORDER BY db_names) AS row_num, db_names
FROM mysql_testing
ORDER BY db_names;

Two small warnings if you still run the old methods on a new server. First, in MySQL 8.0 row_number is a reserved word, so the alias in the scripts above needs a new name like row_num, or backticks around it. Second, setting a user variable inside a SELECT expression is deprecated in MySQL 8.0, and MySQL does not guarantee the order in which such expressions are evaluated. The variable method was a clever workaround for older versions, and it served us well for years. On a current server, ROW_NUMBER() is simpler, safer and much easier for the next person to read.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.