MySQL – Reset Row Number for Each Group – Partition By Row Number

In earlier post, we have learnt how to generate row number for each row using a variable as MySQL does not have any system function like SQLServer’s row_number() to generate the row number. In this post we will see how to reset row number for each group (Alternate method for SQL Server’s row_number() over Partition by method).

Let us use the following data

CREATE TABLE mysql_testing(db_names VARCHAR(100));
INSERT INTO mysql_testing
SELECT 'SQL Server' UNION ALL
SELECT 'MySQL' UNION ALL
SELECT 'SQL Server' UNION ALL
SELECT 'MongoDB' UNION ALL
SELECT 'SQL Server' UNION ALL
SELECT 'MySQL' UNION ALL
SELECT 'Oracle' UNION ALL
SELECT 'PostGreSQL' UNION ALL
SELECT 'MongoDB' UNION ALL
SELECT 'SQL Server' UNION ALL
SELECT 'SQL Server' UNION ALL
SELECT 'MongoDB' UNION ALL
SELECT 'MySQL' UNION ALL
SELECT 'Oracle' UNION ALL
SELECT 'MongoDB' UNION ALL
SELECT 'PostGreSQL';

Now you can generate the row number using two variables and reset it for each group of db_names in two methods. The idea is to use two variables one for incrementing the numbers and other that can be used to reset the number in to 1 whenever group value changes.

Method 1 : Set a variable and use it in a SELECT statement and reset whenever group value changes

SET @row_number:=0;
SET @db_names:='';
SELECT @row_number:=CASE WHEN @db_names=db_names THEN @row_number+1 ELSE 1 END AS row_number,@db_names:=db_names AS 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 and reset whenever group value changes

SELECT @row_number:=CASE WHEN @db_names=db_names THEN @row_number+1 ELSE 1 END AS row_number,@db_names:=db_names AS db_names
FROM mysql_testing, (SELECT @row_number:=0,@db_names:='') AS t
ORDER BY db_names;

Both the above methods return the following result

row_number db_names
 1 MongoDB
 2 MongoDB
 3 MongoDB
 4 MongoDB
 1 MySQL
 2 MySQL
 3 MySQL
 1 Oracle
 2 Oracle
 1 PostGreSQL
 2 PostGreSQL
 1 SQL Server
 2 SQL Server
 3 SQL Server
 4 SQL Server
 5 SQL Server

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