Click here to Skip to main content
15,851,711 members
Articles / Programming Languages / SQL
Tip/Trick

What is the Difference between ROW_NUMBER(), RANK() and DENSE_RANK()?

Rate me:
Please Sign up or sign in to vote.
4.97/5 (15 votes)
26 Sep 2014CPOL2 min read 193.6K   21   6
This tip will explain the difference between these three functions.

Introduction

All of these three functions are used to calculate RowID for the result set returned from a query but in a slightly different way.

Row_Number()

This function will assign a unique id to each row returned from the query.

Consider the following query:

SQL
DECLARE @Table TABLE (
      Col_Value varchar(2)
)

INSERT INTO @Table (Col_Value)
      VALUES ('A'),('A'),('A'),('B'),('B'),('C'),('C');

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

After executing it, we will get:

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

As we notice, each and every row has a unique ID.

Rank()

This function will assign a unique number to each distinct row, but it leaves a gap between the groups. Let me explain with a query, we will use the same query we used above with Rank().

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

This query will return:

Col_Value RowID
A 1
A 1
A 1
B 4
B 4
C 6
C 6

As we can see, rowid is unique for each distinct value, but with a gap. What is this gap?

This gap represents number of occurrence. For example: 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.

Dense_Rank()

This function is similar to Rank with only difference, this will not leave gaps between groups.

So if we use the same query used for Rank, then:

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

We will get:

Col_Value RowID
A 1
A 1
A 1
B 2
B 2
C 3
C 3

So it is clear that it generates a unique id for each group and without repetition.

As we are clear now what these functions do, we can use them in different scenarios based on our need.

For example: Row_Number() can be used if we want to skip and fetch some records. Take a look at this query:

SQL
WITH AllRecords
AS (
      SELECT
            Col_Value,
            ROW_NUMBER() OVER (ORDER BY Col_Value) AS 'RowID'
      FROM
            @Table)

SELECT
      Col_Value,
      RowID
FROM
      AllRecords
WHERE
      RowID >= 4
      AND RowID <= 5;

This will return only those rows with RowID 4 and 5. This function is very useful in paging data in SQL instead of in code till SQL SERVER 2012. In SQL Server 2012, Microsoft introduces a new feature Offset Fetch similar to what we did in the above query.

In SQL Server 2012, the above query will be written as:

SQL
SELECT
      Col_Value
FROM
      @Table
ORDER BY
      Col_Value OFFSET 3 ROWS FETCH NEXT 2 ROWS ONLY;

This will skip first 3 rows and will fetch next 2.

Similarly, we can use Dense_Rank() when we need to calculate rowid with Select Distinct.

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

Will return:-
Col_Value RowID
A 1
B 2
C 3

Or when we need a report where ranks clashes. For example: Two students scored same marks and share same division.

We can use Rank() if we don’t want consecutive numbers.

Hope this tip helped you.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Mindfire Solutions
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questiongood job , thanks alot Pin
Eman Mo7amed25-Apr-18 2:55
Eman Mo7amed25-Apr-18 2:55 
PraiseRank functions are clear Pin
Member 1223417329-Dec-15 3:32
Member 1223417329-Dec-15 3:32 
Questionwell done Pin
Sanath Shetty28-Dec-14 1:48
Sanath Shetty28-Dec-14 1:48 
QuestionNice Ankit Sir Pin
aarif moh shaikh30-Sep-14 22:43
professionalaarif moh shaikh30-Sep-14 22:43 
QuestionNice tip! Pin
Volynsky Alex29-Sep-14 13:15
professionalVolynsky Alex29-Sep-14 13:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.