Top 6 SQL queries asked in interviews – SQL Functions | You must answer

In this article, we will try to understand the top 6 SQL queries and frequently used functions which an interviewer can ask in the interviews. Below are the top 4 functions which you must answer when the interviewer asked.

  1. Row Number
  2. Partition
  3. Rank
  4. Dense Rank

If you can completely read this article, you will be able to understand the most important SQL functions and frequently asked top 6 SQL queries during interviews.

In order to understand these four functions, you can see that I have created a very simple table called as the Sales Table and the sale table has four columns Customer Name, Product Name, Amount and Vendor Name.

You can see some records here

  • 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.
Top 6 Sql queries

Now the data over here is very nice what I would like to do is 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 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
  • Rajendra has bought  Pens from Archies which is Order Number 5

I would like to go and generate unique number 1,2,3,4 and 5. Now many people can say just go ahead and create an identity column but I would like to avoid the identity column and probably let us say that I want to generate these number 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.

The Row Number Function actually generates a unique number for row what I can do here is I can go and write the query like this.

Row Number


If you can execute the above query it will generate one extra column on the fly and here as an order number.

Top 6 SQL queries

So, 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.

You can 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 but the Row Number function actually generates a unique number.

So good till here,

Now what I would like to do is I would like to generate a unique number as per Vendor Name

For example, here you can see how this order number is actually on the complete row. In other words irrespective it is Archies or it is 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 number like 1, 2 and 3.

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

So what we can do is we can go and partition on vendor Name.

We have two partitions here Archies and Bata and then we can start numbering as per the partition.

So the Syntax is row number () exactly the same, we want to generate the unique number but we want to generate on Vendor Name.

So I would like to now give order number as per vendor

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

So our query would be something like this.

Sample query used on row number

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

Archies 1, 2 and 3

Bata 1 and 2

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)

Top 6 SQL queries

So by using a partition, we can group records and then we can apply 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.

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.

Now we want to generate unique customer ID. We have generated Unique Order Number. All fine.

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

But now I would like to go and generate unique numbers as per customer. In other words you can see Rajendra and Rajendra.  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 right?

Partition example

What I would like to see is the same customer number allocated to the same customer irrespective of Row Number and that where we have the Rank Function.

Top 6 SQL queries

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

Top 6 SQL queries

Now every customer name has assigned a unique number. In Rank what happens, it again gives a unique number but if the data repeats right it 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 he 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

Top 6 SQL queries

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 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 continues passion. that’s where we can use something called Dense Rank.

Top 6 SQL queries

dense_rank () over (order by customer name) as customer order number

dense rank use

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

Now we understand Row Number, Partition, Rank and Dense Rank and we also covered top 6 SQL queries commonly asked during interviews.

I hope you enjoyed this article on top 6 SQL queries asked in the interviews. If you feel it is really useful please leave your comments in the comment section.

You can also check What is the difference between Union and Union ALL – Interview questions on SQL here.

Would you like to know more about SQL functions ? You can check here.

Thank You.  Have a good day.

Leave a Reply

Your email address will not be published. Required fields are marked *