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

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 466
    Comment on it

    ROW_NUMBER()

    This function is used to assign a unique id to each row returned by the sql query fired.

    RANK()

    This function is similar to ROW_NUMBER() with the only difference it leaves a gap between the groups i.e this function assign a 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.   

    Example:

    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 ROW_NUMBER():

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

    Output:

    A 1
    A 2
    A 3
    B 4
    B 5
    C 6
    C 7

    In above output a unique row id to each row returned.

    Using RANK():

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

    Output:

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

    In above output value ‘A’ is repeated thrice and has rank ‘1’, the next rank will be 1+3=4. Same with the next value 4+2=6 and so on

 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: