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.

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

Solarwinds
, ,
Previous Post
SQL SERVER – Retrieve Maximum Length of Object Name with sp_server_info
Next Post
MySQL – Reset Row Number for Each Group – Partition By Row Number

Related Posts

6 Comments. Leave new

  • hi Pinal,

    Can we generate row number over a column and partition by a column as in MS SQL.

    -tarun

    Reply
  • how to get “row number” in case using “group by”?

    Reply
  • blakecrittenden
    November 26, 2015 2:03 am

    Thanks for method 2, I was looking for that all over.

    Reply
  • This helped, thanks

    Reply
  • Anthony Rivera
    July 11, 2016 10:19 pm

    Thanks for method #2 I was looking for that method all over the web. MySQL lacks for ROWID() like oracle. This helped me on my php application where using method #1 doesn’t works cause php/pdo executes only the first statement and was ignoring the consecutive statements. Hope this helps people with multiple statements using php/pdo and database engines that doesn’t support and dimple thing like ROWID().

    Reply
  • Great… Method #2 works for me….. Thanks

    Reply

Leave a Reply

Menu