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

Row_Number()

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

Consider the following query:

After executing it, we will get:

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 withRank().

This query will return:

As we can see,rowidis 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 toRankwith only difference, this will not leave gaps between groups.

So if we use the same query used forRank, then:

We will get:

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:

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 withRowID 4and5. 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 featureOffset Fetchsimilar to what we did in the above query.

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

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 useDense_Rank()when we need to calculaterowidwithSelect Distinct.

SELECT DISTINCT  Col_Value,  DENSE_RANK() OVER  (ORDER BY  Col_Value) AS 'RowID' FROM @Table;Will return:-

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

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