April 05, 2012

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'


dense-rank-data

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.

result


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

2 Responses to "Dense Rank - MySQL vs SQL Server"
  1. Anonymous said...
    April 3, 2013 at 10:50 PM

    In MySQL, what if I want result like this instead:

    sno name
    1 Clara
    1 Jerald
    2 Jerald
    1 John
    1 Kant
    2 Kant

  2. Justin Phillips said...
    June 15, 2014 at 12:25 AM

    Thank you very much Madhivanan. This helped me with a ranking problem i was struggling with tonight. I truly appreciate people like yourself who take the time to share experience to help others.

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions