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/