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.

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