15,850,685 members
Articles / Programming Languages / SQL
Tip/Trick

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

Rate me:
26 Sep 2014CPOL2 min read 193.5K   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.

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

 First Prev Next
 good job , thanks alot Eman Mo7amed25-Apr-18 2:55 Eman Mo7amed 25-Apr-18 2:55
 Rank functions are clear Member 1223417329-Dec-15 3:32 Member 12234173 29-Dec-15 3:32
 Hi, You have clearly explained the differences of Rank,Rownum in a easier way. Keep up. Thanks Luke
 well done Sanath Shetty28-Dec-14 1:48 Sanath Shetty 28-Dec-14 1:48
 Nice Ankit Sir aarif moh shaikh30-Sep-14 22:43 aarif moh shaikh 30-Sep-14 22:43
 Nice tip! Volynsky Alex29-Sep-14 13:15 Volynsky Alex 29-Sep-14 13:15
 Last Visit: 31-Dec-99 19:00     Last Update: 3-Mar-24 18:03 Refresh 1