**Row_Number()****Rank()****Dense_Rank()****Ntile()**

**Row_Number() :-**

**Row_Number()**function generate a sequence of numbers starting from 1 up to no of records we are selecting. It means it generates a unique id for each record in a sequence. Lets take an example to make it more understandable.

**Syntax of using Row_Number() function :-**Row_Number() over(partition by [field Name] order by [Field name]) Whenever we use

**Row_Number()**function we need to use Over clause, It means on which field we want to generate the row number. Over clause is mandatory and there are two parameters in over clause where one parameter is mandatory to specify.

**Partition by [field Name]–> Optional**

**Order by [field Name]–> Mandatory**

In the above picture, I have written a query using **row_number()** function and included order by parameter in my query but not included the partition by parameter in my query. you can see the result set that **row_number()** is returning a sequence of numbers starting from **1 to 5**. As we have 5 records in the **Employee** table, I hope you got the point. Now lets take another example to see what happens when “**partition by**” parameter is used in the same query. I am going to add few rows in the table.

**partition by**” in the over clause.

**column**) “

**Name**“, It means that SQL server will make a group of same values present in the column “

**Name**” and treat that group as one partition. In that way we will have(

**5 groups, “dheeraj”,”neeraj”,”raj”,”shayam” and “vikas”**). As we all know that

**row_number()**function generate a sequence no for each row, but here we have included partition parameter as well so first SQL server will break the result set in partition and then it will apply sequence no to each row in each partition. But the only difference is that for each partition, It resets the

**row_number()**value to 1. Hence for “

**dheeraj**” we have

**row_number()**value 1, then for “

**neeraj**” we have

**row_number()**value(1 to 3) and so on.

**Rank() :- Rank()**function used for ranking and ranking could be in ascending or descending order. Lets take an example. I am going to run the below query in management studio and lets see what result comes up.

Lets have a look on the result set, I have used “**Name**” column as a order by parameter, Now look at the result of **Rank()** function, following are the observations.

**name**” in order by parameter,

**Rank()**function will give rank based on “

**Name**” Column. So first row will be ranked as value(1) and 2nd to 4th row will be ranked as 2 as names are same in all 3 rows. Similarly in 5th to 8th row “

**name**” value is same hence these rows will have same rank value i.e. 5.

**Question:-**Why rank value is 5 for 5th to 8th row, while earlier rank was 2 , hence it should be 3.

**Answer:-**

**Formula for rank function is**

**:->>**

**Next Rank=Last Rank+No of rows with same rank(Last rank value)**

**next rank is 9= Last rank(5) + No of rows (4) =5+4**

In the above query, I have changed the order by parameter as desc. hence the result set is different.

**Partition by**” parameter in Rank function and see how SQL server process the above query.

** ****Step 1:- As we have put “name” column in the “partition by” parameter, same names will form a group or partition.**

**Step 2:- Since in “order by” parameter we have put “EID” SQL Server sort the EID value in ascending order in each group or partition.**

**Step 3:- Now rank function give the rank based on EID as we are order the result set with EID.**

**First row is getting rank value 1. Now 2nd row is also getting same rank value(1), this is happened because its a different partition or group based on Name. Similarly 5th row is also getting rank value(1) , because it belongs to other partition and we know that in each partition rank functions reset their values.**

**Dense_Rank() :-**There is not much difference between

**Rank()**and

**Dense_Rank()**function, the only difference between

**Rank()**and

**Dense_Rank()**is that in case of same record the above formula explained by me for next rank got failed.

**->>**

**Next Rank=Last Rank+No of rows with same rank(Last rank value)**

**New formula would be –>**

**Next Rank=Last Rank +1**

**dense_rank()**value 1.

**dense_rank()**value 2.

**dense_rank()**value 3 (which was 5 for rank() function)–>only difference between Rank() and Dense_Rank()

**NTILE() :-**

**NTILE()**function does logical grouping of records and assign sequential no to each group and each record within a group has same group no. Lets take an example and see how

**NTILE()**function works.

**NTILE()** function takes an additional parameter **NTILE(n)** where n is the no of logical grouping we want to form. I have set the value **n=2**. Lets have a look on the result set and understand the formula.

**Total no of records in the table / No of logical grouping**

**10/2= 5**

**Formula= [Total no of records in the table] / [No of logical grouping]**

The above picture clearly depicts that first four rows are having 1 as logical group no and second logical group is having 3 rows with logical no 2. and third logical group is having 3 rows with logical no 3. Lets take another example where n=4.

I hope this article will help you a lot to understand Rank functions. I will talk about CTE(**Common table expression**) in my next article. Till then take care and have a wonderful day.

954 total views, 2 views today

#

what is the difference when we are using Row_Number() and Rank() both with Partition by field as both returing the same result..?

select ROW_NUMBER() over(partition by Name order by EID) ,EID ,Name from Employee

select RANK() over(partition by Name order by EID),EID ,Name from Employee