Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Differece between ROW_NUMBER(), RANK() and DENSE_RANK() in SQL Server

    • 0
    • 1
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 340
    Comment on it
    1. ROW_NUMBER()
    2. RANK()
    3. DENSE_RANK()

     

    All of these three functions are used to calculate the Id of row but in different way.

    I am using the below script for examples

    
    
    CREATE TABLE Marks
    (
    SubjectId INT,
    Marks INT
    )
    INSERT INTO Marks VALUES(1,100),
                             (2,90),
    						 (3,80),
    						 (4,70),
    						 (5,70),
    						 (6,50),
    						 (7,40),
    						 (8,40),
    						 (9,30)
    
    
    

     

    1. ROW_NUMBER():-

        This function will assign the unique Id to each row of result.

     

      

     

    Please see the result in above image. ROW_NUMBER() has assigned unique Id to each record

     

    2) Rank() :-

     This function works in same way as ROW_NUMBER() except the "equal" records are ranked the same. This will  leave the gap of between the same values. please see the below result

     

     

    As we can see Marks with value 70 is assigned the same rank 4 but for the next value it gave rank 6 it skipped the rank 5. Same case is for value 40

     

    3) DENSE_RANK() :-

     

    This function is same like RANK() but it does not leave the gap when assigning the rank. it is “dense”. Please refer the below image

     

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: