In this article, **we’ll **understand **what’s **Row Number, Partition, Rank, and Dense Rank. These are frequently used SQL functions that an interviewer can ask during the interviews.

Let us dig into this topic and try to understand **Row Number, Partition, Rank and Dense Rank** with practical examples.

- Row Number
- Partition
- Rank
- Dense Rank

**What are Row Number, Partition, Rank, and Dense Rank**

In order to know these four functions, you will see that I even have created a very simple table is named the Sales and this Sales table has four columns sort of a customer name, a product name and an amount, and a Vendor Name.

You can see **I even have **inserted some records in customer table.

- Shiv has bought
from*Shoes**Bata*

- Raju has bought
from*Bags**Bata*

- Sukesh has bought
from*Perfume**Archie’s*

- Rajendra has bought
and*Books*from*Pens*etc.*Archie’s*

Now the data over here is extremely nice. What i’d wish to do is i’d wish to go and generate a singular order number for everybody among these orders placed.

In other words, **I might wish to **go and generate a running number** here **as given below.

- Shiv has bought
from*Shoes*which is Order Number 1*Bata*

- Raju has bought
from*Bags*which is Order Number 2*Bata*

- Sukesh has bought
from*Perfume*which is Order Number 3*Archies*

- Rajendra has bought
from*Books*which is Order Number 4*Archies*

- Again Rajendra has bought
from*Pens*which is Order Number 5*Archies*

I would wish to go and generate unique numbers like 1,2,3,4 and 5 for these orders. Here many folks say just go ahead and make an identity column, but i’d wish to avoid the word identity column probably I can say that i might wish to go and generate these numbers on the fly.

In other words, **I might wish to **generate these numbers while I do the selection of the SQL and that’s where **we will **use the Row Number function.

**Use of Row_Number() Function in SQL server or Oracle**

The Row Number Function actually generates **a singular **number **for every **row. I can write a below query **to realize **this unique number **for every **order placed.

If **you’ll **execute the above query **it’ll **generate one extra column on the fly. Here **you’ll **see the expected order number.

In other words, the Row Number function helps you to form a singular number by employing an easy select statement. You’ll also use this unique number in several contexts.

We even have seen plenty of developers using this unique number for page nation. So no matter the utilization it is the Row Number function actually generates a singular number for each row.

So good till here?

Now what I’d wish to try do is I’d wish to get a singular number as per Vendor Name

In the table, you will see how this order number is basically on the whole row no matter Archies or Bata it’s just numbering them 1,2,3,4, and 5.

*Now what i’d wish to try to do is, i’d wish to mention that if it’s Bata then give the order number like 1 and a few of. If it’s Archies then start with new order numbers like 1, 2, and 3.*

**Use of Partition in SQL Server or Oracle**

**How do I go now and generate order number vendor wise? That’s where we can go and use partitions **

Now here **we will **partition on vendor Name.

We have two partitions here Archies and Bata. Now **we will **start numbering as per the partition.

So the Syntax **is the **same as row number (). But now **we would like to go **and generate **a singular **number **supported **by Vendor Name.

Let us write **a question to offer **the order number as per vendor

**row_number () over (partition by Vendor Name Order By Vendor Name)**

So our query would be something like this.

You can see over here **Vendor Order Number** has generated two partitions

- For Archies 1, 2 and 3

- For Bata 1 and 2

Here **within the **table below First Order Number is generic Order Number whereas Second-Order Number is Vendor Order Number Partition by Vendor Name.

`Row_number () over (partition by vendor name order by vendor name) - Is used to apply a partition on vendor name`

So by **employing a **partition, **we will **group the records **then we will **apply a Row Number function **thereon **.

The first column shows order **no 1**,2,3,4 and 5. Now, **check out **the second column vendor order number for Archies **it’s **given 1, 2, and 3.

For batá, **it’s **started giving the numbers differently like 1 **and 2 **.

**Difference Between Row_Number and Partition **

So by using partition **we will **actually go and group records **then we will **apply the Row Number function **thereon**.

When I use row numbers it’ll just give unique values throughout the rows and second, once i exploit a Partition with the Row Number it’ll actually create groups then give Row Numbers according to the groups.

Now we have generated a Vendor Order number partition by Vendor Name that works fine.

We have generated a Vendor Order number partition by Vendor Name that works fine.

Also we have generated Vendor Order number partition by Vendor Name that works fine.

But now **I** might wish to go and generate unique numbers as per customer. Here you’ll see Rajendra and Rajendra that aren’t correct right?. I** might wish to **generate **a singular **number for Rajendra.

*If you’ll see the below table here it’s generated 1 and a couple of for Rajendra. which isn’t correct*

What **i might wish to **see **is that the **same customer number allocated to **an equivalent **customer **regardless of **Row Number. That’s where we have** **the Rank Function.

**Rank and Dense_Rank in SQL Server With Examples. **

Now let us see the ** rank and dense_rank in sql server with examples**.

**Use of Rank Function **

*rank () over (order by customer name) as customer order number*

Now every customer name has assigned a unique number.

**What is the problem with a Rank function when there is a repeated data?**

In Rank what happens, it again gives **a singular **number but if **the info **repeats right, for **an equivalent quite **data it **doesn’t **go and generate **a singular **number.

In other words for Sukesh, **it’s **generated **a singular **number 5 and for Raju, it generated **a singular **number 3. For Rajendra, it generated **a singular **number but as soon **because it **found Rajendra again it **didn’t **generate **a singular **number.

So if there are repeated data right the Rank Function will use **an equivalent **number for the repeated data.

Now **there’s little **problem here especially in Rank **you’ll **see **up here**

1 for Rajendra **that’s **good and again 1 for Rajendra **that’s **also good but where has **the two **gone but suddenly **I even have **3 for Raju and 4 for Shiv and 5 for Sukesh.

I would like to see rank number 2 for Raju why he has skipped a value right?

I want him to use a second value and **i would like **him to use **a worth **in continuous passion. that’s where **we will **use something called Dense Rank.

**Use of Dense Rank Function **

**Dense Rank Query and Results **

`Dense_rank () over (order by customer name) as customer order number`

Now he is using continuous numbers. So in Rank what happens it skips continuous numbers depending on how much-repeated data we have.

**Delete duplicate rows using dense_rank() in SQL**

Now let us see how to *delete duplicate rows using dense_rank() in sql*.

Let us consider the below customer table.

If you can observe the above table all 3 records got duplicated.

Now let us write a query using dense_rank() to select these records.

First let us capture these records using common table expression.

With CustomerCTE as (Select ID, Customer Name, item, price, dense_rank() over (partition by Id order by Id) as DENSERANK from customers) select * from CustomerCTE

Result for this query will be

Now let us write a query to delete the records where dense_rank() is more than 1.

For that the query will be.

With CustomerCTE as (Select ID, Customer Name, item, price, dense_rank() over (partition by Id order by Id) as DENSERANK from customers) delete from CustomerCTE where DENSERANK>1;

Now the results for this query is given in the below table.

Now we got the output as we expected.