March 12, 2012

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-row-number-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.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

0 Responses to "Generate Row Number – SQL Server vs MySQL"
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions