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 (http://blog.sqlauthority.com)

7 thoughts on “MySQL – Reset Row Number for Each Group – Partition By Row Number

  1. Is there a way to do this and write the result back to the original table? For example, if I include a row_number in the table mysql_testing that consists of all NULL values after populating with the db_names, can I write row_number to the table in a subsequent query?

    Like

  2. Is there a way to add the results back to the current table? If the table db_names has a column seq_number that is NULL, can I write the generated row_number to seq_number?

    Like

  3. Thanks but your solution does not work on mariadb. And I found that I need to add an alias to the table to success.

    SELECT @row_number:=CASE WHEN @db_names=db_names THEN @row_number+1 ELSE 1 END AS row_number,
    @db_names:=a.db_names AS db_names
    FROM mysql_testing a
    ORDER BY a.db_names;

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s