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

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