locked
What is the difference between RANK and Dense Rank usage in sql server RRS feed

  • Question

  • User264732274 posted

    please post two sample which explain the difference between RANK and Dense Rank usage in sql server and when to use rank and when to use dense rank. thanks

    Thursday, November 10, 2016 2:01 PM

Answers

  • User-967720686 posted

    Hi, 

    Rank() and Dense_Rank() gives you ranking within your ordered partition the difference being Rank() will skip the next ranking if there are ties but Dense_Rank() will now. Run the query below. Have a close look at the value of Rank and DRank columns of row number 3.

    Declare @Table Table (
    	DeptID Int, 
    	Name Varchar(10)
    )
    
    Insert Into @Table (DeptID, Name)
    Values (1, 'Farhan'),
    	   (2, 'Jason'),
    	   (1, 'Farhan'), 
    	   (2, 'John'),
    	   (1, 'Richard')
    
    
    Select	RANK() Over (Partition By DeptId Order By Name) As [Rank],
    		DENSE_RANK() Over (Partition By DeptId Order By Name) As [DRank],
    		DeptID, 
    		Name 
    From	@Table 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 10, 2016 10:52 PM
  • User-967720686 posted

    Hi Sudip, 

    Below is the output of the SQL. Look at the Rank / DRank column value of Richard. Using Rank() the value is 3 as previous two were ties Rank() skipped 2 but DenseRank() does not. Hope that explains. 

    Rank                 DRank                DeptID      Name
    -------------------- -------------------- ----------- ----------
    1                    1                    1           Farhan
    1                    1                    1           Farhan
    3                    2                    1           Richard
    1                    1                    2           Jason
    2                    2                    2           John



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 11, 2016 6:40 AM

All replies

  • User-967720686 posted

    Hi, 

    Rank() and Dense_Rank() gives you ranking within your ordered partition the difference being Rank() will skip the next ranking if there are ties but Dense_Rank() will now. Run the query below. Have a close look at the value of Rank and DRank columns of row number 3.

    Declare @Table Table (
    	DeptID Int, 
    	Name Varchar(10)
    )
    
    Insert Into @Table (DeptID, Name)
    Values (1, 'Farhan'),
    	   (2, 'Jason'),
    	   (1, 'Farhan'), 
    	   (2, 'John'),
    	   (1, 'Richard')
    
    
    Select	RANK() Over (Partition By DeptId Order By Name) As [Rank],
    		DENSE_RANK() Over (Partition By DeptId Order By Name) As [DRank],
    		DeptID, 
    		Name 
    From	@Table 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 10, 2016 10:52 PM
  • User264732274 posted

    i run your script but do not understand the diff between rank and dense rank in terms of out put.

    would u please explain what is happening. thanks

    Friday, November 11, 2016 5:37 AM
  • User-967720686 posted

    Hi Sudip, 

    Below is the output of the SQL. Look at the Rank / DRank column value of Richard. Using Rank() the value is 3 as previous two were ties Rank() skipped 2 but DenseRank() does not. Hope that explains. 

    Rank                 DRank                DeptID      Name
    -------------------- -------------------- ----------- ----------
    1                    1                    1           Farhan
    1                    1                    1           Farhan
    3                    2                    1           Richard
    1                    1                    2           Jason
    2                    2                    2           John



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 11, 2016 6:40 AM