Reset The Row Number for Each Group Partition by Row Number

In the earlier post, we learned how we can generate row number of each row using a variable because MySQL does not have any system function like SQLServer’s row_number(). Now in this post, we’ll show you how to reset row number for each group.

Let’s look at this data as an example:

CREATE TABLE mysql_testing(db_namesVARCHAR(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’;

You can generate row number using variables and reset for each group of db_names in two methods.

Read the rest of the tutorial in this post: http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/