Generate Row Number – SQL Server vs MySQL

In this series, we will see how same SQL tasks can be achieved differently in SQL Server and MySQL. Generating a row number  or a serial number is a common requirement when showing the resultsets.

In SQL Server, starting from version 2005, we can make use of the row_number() function

Consider the following set of data

SQL Server


Using the row_number() function we can generate a serial number as follows:

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

MySQL


Using a variable in MySQL, we can generate a serial number as follows:

set @sno:=0;
select @sno:=@sno+1 as sno,names from test
order by names;


In the above example, variable @sno gets incremented by 1 for each row.

Stay tuned for more on MySQL vs SQL Server posts.

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