In this article, we will see what is Row Number, Partition, Rank and Dense Rank. These are frequently used SQL functions that an interviewer can ask during the interviews.
Let us understand Row Number, Partition, Rank and Dense Rank with practical examples.
- Row Number
- Dense Rank
Rownumber and Partition with Practical Examples
In order to understand these four functions, you can see that I have created a very simple table called the Sales and this Sales table has four columns Customer Name, Product Name, Amount and Vendor Name.
You can see I have inserted some records in customer table.
- Shiv has bought Shoes from Bata
- Raju has bought Bags from Bata
- Sukesh has bought Perfume from Archies
- Rajendra has bought Books and Pens from Archies etc.
Now the data over here is very nice. What I would like to do is I would like to go and generate a unique order number for each one of these orders placed.
In other words, I would like to go and generate a running number over 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 like to go and generate unique numbers like 1,2,3,4 and 5 for these orders. Now many people say just go ahead and create an identity column, but I would like to avoid the word identity column Probably I can say that I want to go and generate these numbers on the fly.
In other words, I would like to generate these numbers while I do the select of the SQL and that’s where we can use the Row Number function.
Use of Row_Number() Function in SQL server or Oracle
The Row Number Function actually generates a unique number for each row. I can write a below query to achieve this unique number for each order placed.
If you can execute the above query it will generate one extra column on the fly and here you can see the order number.
In other words, Row Number function helps you to create a unique number by using a simple select statement and then you can use this unique number in different contexts.
You can also use this unique number for page nation. I have seen a lot of developers using this unique number for page nation. So whatever the use it is the Row Number function actually generates a unique number for each row.
So good till here,
Now what I would like to do is I would like to generate a unique number as per Vendor Name
In the table, you can see how this order number is actually on the complete row irrespective of Archies or Bata it is just numbering them 1,2,3,4 and 5.
Now what I would like to do is, I would like to say that if it is Bata then give the order number like 1 and 2. If it is 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 can partition on vendor Name.
We have two partitions here Archies and Bata. Now we can start numbering as per the partition.
So the Syntax is the same as row number (). But now we want to go and generate a unique number based on Vendor Name.
Let us write a query to give 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 in 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)
So by using a partition, we can group the records and then we can apply a Row Number function on it.
The first column is Order Number 1,2,3,4 and 5. Now, look at the second column vendor order number for Archies it has given 1, 2 and 3.
For Bata, it has started numbering them differently as 1 and 2.
Difference Between Row_Number and Partition
- So by using partition we can actually go and group records and then we can apply the Row Number function on it.
- When I use row number it will just give unique values throughout the rows and second, when I use a Partition with the Row Number it will actually create groups and then give Row Numbers according to the groups.
We have generated Vendor Order number partition by Vendor Name that works fine.
But now I would like to go and generate unique numbers as per customer. Here you can see Rajendra and Rajendra that are not correct right?. I would like to generate a unique number for Rajendra.
If you can see the below table here it has generated 1 and 2 for Rajendra. which is not correct.
What I would like to see is the same customer number allocated to the same customer irrespective 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 unique number but if the data repeats right, for the same kind of data it does not go and generate a unique number.
In other words for Sukesh, it has generated a unique number 5 and for Raju, it generated a unique number 3. For Rajendra, it generated a unique number but as soon as it found Rajendra again it did not generate a unique number.
So if there are repeated data right the Rank Function will use the same number for the repeated data.
Now there is a small problem here especially in Rank you can see over here
1 for Rajendra that is good and again 1 for Rajendra that is also good but where has the 2 gone but suddenly I 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 want him to use a value in continuous passion. that’s where we can 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 will be
Now we got the output as we expected.
Rank, dense_rank, row_number in oracle are the same as in SQL server.
Now we understood Row Number, Partition, Rank and Dense Rank. These are frequently asked SQL interview questions for developers.
Would you like to know more about SQL functions? You can read this article SQL Server Functions.
I hope this article’ Row Number, Partition, Rank and Dense Rank is useful to you. Please make sure to submit your queries in the comment section below.
Here you can see related article on SQL Aggregate functions.
Thank You. Have a great day.