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';

Solarwinds

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)

Solarwinds
,
Previous Post
MySQL – Generating Row Number for Each Row Using Variable
Next Post
MySQL – How to do Natural Join in MySQL? – A Key Difference Between Inner Join and Natural Join

Related Posts

26 Comments. Leave new

  • Michelle Ufford
    May 21, 2014 3:27 am

    This was *exactly* what I needed. You’re awesome. Thank you, Pinal! :)

    Reply
  • 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?

    Reply
  • 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?

    Reply
    • Why do you want to update the table? You can just use it in SELECT statement. This way you can avoid updates whenever new data are added

      Reply
  • Manish Tiwari
    July 4, 2014 12:06 pm

    Awesome Pinal. Your solutions are awesome. Thanks.

    Reply
  • 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;

    Reply
  • thanks! i’ve seen similar in other places but your coding/example makes the most sense. very de-objuscated.

    Reply
  • What about if I want to reset the row number when status changed?

    Status row num desired result
    A 1 1
    A 2 2
    A 3 3
    A 4 4
    A 5 5
    A 6 6
    D 1 1
    A 7 1
    A 8 2
    A 9 3
    D 2 1
    A 10 1
    A 11 2

    Reply
  • Hello, I have a question. It seems for me that your solution violates the rule described on MySQL manual. You can read:

    “As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement.”

    This is exactly what you are doing with @db_names variable. You read it and then you assign new value – all in one select statement.

    Reply
  • Does it work only when the column which is being checked in the case statement, is a var and not numeric?

    Reply
  • If you wanted to update the row_number back into a new column row_nmbr in the same db_names table, what would the query look like? I have such a use case. Thanks.

    Reply
  • If you wanted to update the row_number back into a column in the same db_names table, what would the query look like? Thanks.

    Reply
  • frizk@nyc-rea.com
    February 18, 2016 1:09 am

    Thank you! This is great! I used method 1., which was successful, but I need to incorporate the row_number to populate into an existing table. I created the row_number column in the desired table, (which is also the table where the data is that I’m referencing in the script-not sure if that matters), and tried to set it to populate using the script you provided w/ minor modifications but Im getting a syntax error. I need to SET the row_number column to populate within the existing table or create a new table that includes all columns from the original table along with the additional row_number column. I would greatly appreciate your guidance on how to achieve this. Thank you.

    Reply
  • Hi Pinal,

    I need to reset the records after each 5th records in below sequence For ex:
    Rn id
    1 1
    2 2
    3 3
    4 4
    1 5
    2 6
    3 7
    4 8

    I want to create Rn from above tables.
    Appreciate for your help.

    Reply
  • Thanks for this post.
    I did something a little different, I wanted to have the strings to have the same identifier, so I did:

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

    That gave me:

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

    Exactly what I needed.

    In the case of my DB I have
    game
    gm_id[auto_increment], opp[string], (other columns)

    I wanted to assign an Identifier to each different foe so I added a column op_id and used an UPDATE statement using the above.

    set @a:=0;
    set @b:=”;

    UPDATE game as o
    INNER JOIN (SELECT @a:=CASE WHEN @b=opp THEN @a:=@a ELSE @a+1 END AS a,@b:=opp AS b,gm_id AS c
    FROM game ORDER BY opp) As s on o.gm_id = s.c
    SET o.op_id=s.a
    WHERE o.opp=s.b

    Worked great! Couldn’t have done this without you!

    Reply
  • What I did will help you… if your existing table has an unique identifier for each row this will work for sure

    SET @rn:=0;
    SET @names:=’ ‘;

    UPDATE existing_table AS et
    INNER JOIN (SELECT @rn:=CASE WHEN @names=db_names THEN @rn+1 ELSE 1 END AS row_rumber,@names:=db_names AS b, unique_identifier AS c FROM existing_table ORDER BY db_names) AS temp
    ON et.unique_identifier = temp.c
    SET et.row_number=temp.a
    WHERE et.names=temp.b

    You need to use aliases in the update statement and then give the selection table its own alias for it to work.

    Reply
  • Thank you so much for sharing.
    I got the exact one, which i am looking.

    Reply
  • christianmanrique123
    June 2, 2017 10:33 pm

    this is really awesome and so helpful for me, thanks for sharing this i appreciated

    Reply
  • My result is the following output.
    It only adds a row number if the db_name is in the next row of the table.
    Why is it going wrong? and how can I solve?

    Thanks a lot

    row_number db_names
    1 MongoDB
    1 MongoDB
    1 MongoDB
    1 MongoDB
    1 MySQL
    1 MySQL
    1 MySQL
    1 Oracle
    1 Oracle
    1 PostGreSQL
    1 PostGreSQL
    1 SQL Server
    1 SQL Server
    2 SQL Server
    1 SQL Server
    1 SQL Server

    Code:
    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’;

    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:=a.db_names AS db_names
    FROM mysql_testing a
    ORDER BY db_names;

    Reply
  • Dear all why this query inst running using PHP script and it is working well in phpmyadmin…? where am i making mistakes ?

    $QRS = “SET @x= 0;
    UPDATE `stud_master`
    SET `srno` = (SELECT @x := @x + 1) where `cent_code` = $A “;
    mysql_query($QRS);

    Reply

Leave a Reply

Menu