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)
29 Comments. Leave new
This was *exactly* what I needed. You’re awesome. Thank you, Pinal! :)
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?
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?
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
Awesome Pinal. Your solutions are awesome. Thanks.
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;
thanks! i’ve seen similar in other places but your coding/example makes the most sense. very de-objuscated.
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
I think you need to use one more variable that checks for row number and reset the count
did you find a solution to this?
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.
Does it work only when the column which is being checked in the case statement, is a var and not numeric?
I think it will work for any data type. It is just the comparison
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.
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.
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.
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.
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!
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.
Thank you so much for sharing.
I got the exact one, which i am looking.
I am glad that this helped you.
this is really awesome and so helpful for me, thanks for sharing this i appreciated
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;
Same here, did you fix it?
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);
You have to run every statement for php security. One for SET, One for UPDATE
Hi, first of all thanks for all posts.
I tried your script by I dont get the same result. It does not work.
Result is similar as “Gerdin” comment with a little difference:
I get all 1 as row_number except for last record where I get 2
I know it’s an old post but your help would be great.
thanks in advance