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.

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

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

One thought on “MySQL – Generating Row Number for Each Row using Variable

  1. Pingback: MySQL – Introduction to User Defined Variables | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s