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