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 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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
6 Comments. Leave new
hi Pinal,
Can we generate row number over a column and partition by a column as in MS SQL.
-tarun
how to get “row number” in case using “group by”?
Thanks for method 2, I was looking for that all over.
This helped, thanks
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().
Great… Method #2 works for me….. Thanks