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.