Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Difference between Dense_Rank() and Rank().

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 383
    Comment on it

    Rank()

    This function assigns a row id to each row returned by the sql query fired. It provides distinct row id to distinct values, similar values have similar row id, the next row id is calculated as number of occurrence of previous row id + 1 i.e it leaves gaps between groups.

    Dense_Rank() 

    This function also provides a row id to each row returned by the sql query fired therefore is similar to Rank() function with only difference, this will not leave gaps between groups.

    Example demonstrating difference between above two function:

    Decalring a table variable and inserting values into it: 

    DECLARE @Table TABLE (
    Col_Value varchar(2)
    )
    INSERT INTO @Table (Col_Value)
    VALUES ('A'),('A'),('A'),('B'),('B'),('C'),('C');

    Using Rank() function in a query:

    SELECT
    Col_Value,
    Rank() OVER (ORDER BY Col_Value) AS 'RowID'
    FROM
    @Table;

    Output of above query:

    A 1
    A 1
    A 1
    B 4
    B 4
    C 6
    C 6

    Using Dense_Rank() function in a query:

    SELECT
          Col_Value,
          DENSE_RANK() OVER (ORDER BY Col_Value) AS 'RowID'
    FROM
          @Table;

    Output of above query:

    A 1
    A 1
    A 1
    B 2
    B 2
    C 3
    C 3

    In output it can be clearly seen that Dense_Rank() does not leave gaps between groups.

 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: