Generate Row Number for Each Row Using Variable

MYSQL doesn’t have any system function like in SQL server where SQL Server uses row_number() to generate to row number for each row. However, this can be generated using the variable in the SELECT statement.

In this example below, the table has five rows:

CREATE TABLE mysql_testing(db_namesVARCHAR(100));
INSERT INTO mysql_testing
SELECT ‘SQL Server’ UNION ALL
SELECT ‘MySQL’ UNION ALL
SELECT ‘Oracle’ UNION ALL
SELECT ‘MongoDB’ UNION ALL
SELECT ‘PostGreSQL’;

You can generate two row number using a variable in two methods:

1. Set the variable and use it in a SELECT statement.

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

Or use the second method as demoed in this article: http://blog.sqlauthority.com/2014/03/08/mysql-generating-row-number-for-each-row-using-variable/