Reset Row Number For Each Group - SQL Server Vs MySQL

Continuing on my SQL Server vs MySQL series, we will see how same things can be done differently in SQL Server and MySQL

Generating a row number  or a serial number and resetting it on each group is a common requirement when showing result sets. In SQL Server, starting from version 2005, we can make use of the row_number() function with the partition clause

Consider the following set of data
sql-data

SQL Server


Using the row_number() function, we can generate the serial number and reset for each names

select row_number() over (partition by names order by names) as sno,names from test


MySQL


Using a variable, we can generate the serial number, and use another variable that resets first variable for each group

set @sno:=0;
set @names:='';



OUTPUT


result


About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan

3 comments:

Cottora said...

Madhivanan - this is a very interesting way to reset the counter via MySql. However, I am having trouble getting the trigger to be "triggered".

It seems that sno should be reset when the name is null. However when is the name ever null? Is rollup or some other break causing statement to be used so that name is caused to be null?

Thanks you, Cottora

Unknown said...

http://stackoverflow.com/questions/2026956/want-row-number-on-group-of-column-in-my-sql

Here's a way using ddl..

Nandakumar AS said...

Madhivanan - the query helped me to resolve the issue to create the sequence number of my requirement. However, in the example displayed in your blog, how to avoid displaying sno for the rows where there is one record and display sno where there are more than 1 records with same name. Any suggestion would be great help.