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


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
subscribe via rss subscribe via e-mail
print this post follow me on twitter

About The Author




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