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
- 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 Shoes from Bata
- Raju has bought Bags from Bata
- Sukesh has bought Perfume from Archie’s
- Rajendra has bought Books and Pens from Archie’s etc.
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 Shoes from Bata which is Order Number 1
- Raju has bought Bags from Bata which is Order Number 2
- Sukesh has bought Perfume from Archies which is Order Number 3
- Rajendra has bought Books from Archies which is Order Number 4
- Again Rajendra has bought Pens from Archies which is Order Number 5
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.