What is the difference among Row_Number, Rank and Dense_Rank

Sudip Bhatt 2,276 Reputation points
2020-12-27T07:29:53.587+00:00

please discuss with a easy example which help me to understand when to use Rank function and when to use Dense_Rank function.

When row_number used with partition by and same way Dense_Rank used with partition by then getting same output.

i really like to understand what will be the difference in output when row_number will be used with partition by example and same Rank / Dense_Rank will be used with partition by example.

looking for good example to discuss the scenario when to use row_number with partition,
when to use Rank with partition & When to use Dense_Rank with partition ?

please discuss three scenario for Row_Number,Rank & Dense_Rank. thanks

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,657 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 112.7K Reputation points MVP
    2020-12-27T11:07:27.377+00:00

    row_number numbers the rows 1, 2, 3, etc by the columns in the ORDER BY clause, and if there are ties, it is arbitrary which rows that gets the same number.

    rank and dense_rank are similar to row_number, but when there are ties, they will give the same value to the tied values. rank will keep the ranking, so the numbering may go 1, 2, 2, 4 etc, whereas dense_rank will never give any gaps.

    Study this example:

    CREATE TABLE #data(MainID     int           NOT NULL,
                       Component  varchar(10)   NOT NULL,
                       Value      decimal(12,2) NOT NULL,
                       PRIMARY KEY (MainID, Component)
    )
    go
    INSERT #data (MainID, Component, Value)
      VALUES(1, 'ALPHA', 12),
            (1, 'BETA', 12),
            (1, 'GAMMA', 13),
            (1, 'DELTA', 12.23),
            (1, 'EPSILON', 11.1),
            (2, 'London', 9.12),
            (2, 'New York', 8.11),
            (2, 'Mumbai',  9.12)
    go
    SELECT MainID, Component, Value, 
           rowno = row_number() OVER(PARTITION BY MainID ORDER BY Value),
           rank  = rank() OVER(PARTITION BY MainID ORDER BY Value),
           drank = dense_rank() OVER(PARTITION BY MainID ORDER BY Value)
    FROM  #data 
    ORDER BY MainID, Value
    go
    DROP TABLE #data
    

    If there are no ties, the three functions will yield the same result.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. EchoLiu-MSFT 14,581 Reputation points
    2021-01-01T08:35:20.457+00:00

    Hi @Sudip Bhatt ,

    Similar examples for your reference:
    52718-image.png

    52719-image.png

    Regards
    Echo


    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.