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.MySQL - Generating Row Number for Each Row Using Variable rankingimage

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 generate the row number using a variable in two methods

Method 1 : Set a variable and use it 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.