Dense Rank - MySQL vs SQL Server

                                                                                    
Continuing my series on how same things can be done differently in SQL Server and MySQL, in this post, we will see how to implement Dense Rank in SQL Server vs MySQL.

Generating a dense_rank is a common requirement when showing resultsets. In SQL Server, starting from version 2005, we can make use of the dense_rank() function. Dense_rank() will generate the serial number for each set of values and keep the same number if the value is duplicated

Consider the following set of data

create table test(names varchar(100))
insert into test
select 'Suresh' union all
select 'Ramesh' union all
select 'Kant' union all
select 'Jerald' union all
select 'Clara' union all
select 'Ramesh' union all
select 'Kant' union all
select 'Jerald' union all
select 'John'



SQL Server

Using the dense_rank()  function, we can generate a serial number and reset for each name

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

MySQL

Using a variable, we can generate the serial number, and use another variable that keeps same value for duplicates

set @sno:=0;
set @names:='';
select @sno:=case when @names=names then @sno else @sno+1 end as sno,@names:=names as names from test
order by names;

In the above example, variable @sno gets incremented by 1 for each set of values thus keeping the same value for duplicates.