Rank VS Dense_Rank in MS SQL

Today we will some interesting SQL feature. You would have seen in SQL RANK and DENSE_Rank function. Did you ever think why we have these two functions and what exactly is the difference between RANK and DENSE_RANK in MS SQL.

My task for the day is to teach you the difference between RANK and DENSE_Rank in MS SQL.

First I will give a small description about the difference and then we will go through the sample.

RANK—> Rank function will help you to find the RANK within your ordered partition. If you have same rank for the two rows then the next rank will be skipped. Which means if you have 3 items with the same rank as 3 then the next rank will be 6. 4th and 5th rank will be skipped.

DENSE_RANK—> Dense_Rank function will help you to rank the the item within your partition same like RANK function but it will not skip any ranking, it will be consecutive. Which means if you have the same scenario we taken above that is 3 items with the same rank as 3 then the next rank will be 4 in case of dense_rank.

Now lets see the demo.

First we will create the table

CREATE TABLE [dbo].[tblStatistics](

    [ID] [numeric](18, 0) NULL,

    [Name] [varchar](50) NULL,

    [Salary] [numeric](18, 0) NULL,

    [City] [nchar](20) NULL

) ON [PRIMARY]

Lets query the table and see the output,

Above one is the simple select query. Now we will query using RANK and DENSE_RANK together to see the difference.

Rank and Rank_Dense query syntax is slightly different from our regular query as you have to mention the partition in query.

 

SELECT [Name]

      ,[Salary]

      ,[City]

      ,RANK() over (partition by City order by Salary) Rank_Demo

      ,dense_rank() over (partition by City order by Salary) Dense_Rank_Demo

      FROM [tblStatistics]

Lets execute above query and see the result.

You would have noticed that the skipping happened in the RANK for Rank column and consecutive rank for the Rank_Dense column in the above screen print.

That’s all..now you learned the difference between RANK and DENSE_RANK in SQL.

Thanks for reading my article!