SQL Server interview question :- Explain RowNumber,Partition,Rank and DenseRank ?
Articles,  Blog

SQL Server interview question :- Explain RowNumber,Partition,Rank and DenseRank ?


In this video we will try to understand three important functions First one is Row Number second one is Partition and the last one is Rank function so now in order to understand this three functions you can see that i have created a very simple table here called as the sales table and this sales table has four columns here customer name product name, amount, vendor name and you can see some record over here five records Shiv has bought shoes from bata Raju has bought bags from Bata Sukesh has bought perfume from Archies and etc.. etc.. now this data over here is very nice but what we have to like to do is i would like to go and generate a unique order number for each one of this orders placed in other words i would like to go and generate a running number over here saying that OK shiv bought shoes so that is order number 1 and raju bought bag that is order number 2 Sukesh bought perfume order number 3 Rajendra Bought Books order number 4 and again Rajendra bought pens order number 5 so i would like to go and generate a unique number like 1, 2, 3, 4, 5 now many people can say that just go ahead and create an identity column but i would like to avoid the identity column and probably let’s say that i want to just generate these number’s on fly in other words i would like to generate this numbers while i do the select of the SQL that’s where we can use the row number function the row number function actually generates unique number for a row so what i can do here is i can go and change my query something like this i can say here row under score number over order by customer name now i’ll give a nice name to this column order number so what we have saying here is that please use the row number function and sort by customer name and give the naming on this sort orders so in other words what we i’ll do is it will actually a short this customer name and give the numbering accordingly and i have given the column name as order number so if i now go and execute this customer name not customers name so now you can see over here he has a generated or he has created one extra column on the fly here called as order number or you can see the numbering given 1, 2, 3, 4 and 5 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 need to generate a unique number must be or probably you can use this unique number for pagination i have seen lot of developers using this unique number for doing pagination so whatever used 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 that’s there but i would like to generate unique number as per vendor means what for example here you can see now this order number is actually on the complete row in other words irrespective it is Archies or it is Bata right he is just numbering them 1, 2, 3, 4, 5 now what i would like to do is i would like to say that OK if it is a Bata then give the oreder number as 1 and 2 if it is Archies then start with the new order number so i would like to now give order number as per vendor now you can see over here bata has to records so i would like to generate number 1 and number 2 for this for Archies i would like to say OK this is 1 this is 2 and this is 3 OK so how do i go now and generate order number vendor wise and that’s where we can go and use partition so what we can do is we can go and partition on the vendor name we can say OK we have two partitions here one is Archies and other one is Bata and than we can start numbering as per the partition right so the syntax for the creating the partition and than using the row number is absolutely same so what we can do is we can say here that yes we want to generate the unique number but we want to generate on vendor name and we should be partitioning so i’ll say partition on vendor name right so now what this will do is it will actually first go and create a partition on vendor name it i’ll go and create two partitions so on arches and bata and then it will go and apply the row number function so i’ll say this is vendor order number so the first order number was is generic order number generic says unique order number irrespective whatever vendor you have OK and this one this column i’ll be order number vendor wise so if i go and OK partition by it is not on so there is no comma here sorry for the syntax-ex so this is by and we don’t need to put a comma here execute awesome now you can see over here first column is a order number you can see the unique numbers here 1, 2, 3, 4, 5 right now look at the second column vendor order number so you can see now this is archies so for archies he have given 1, 2, 3 as soon as he found there is new vendor bata he has started numbering them differently so you can see for bata he again restarted 1 and 2 so by using partition we can actually go and create you can go and group records and then we can apply the row number function on it so the first function we saw is a row number so when i just use a row number it will just go and give unique values throughout the rows and second when i use a partition with the row number it will actually go and create groups and then give row number according to the groups so for example here you can see the archies and the bata are numbered differently so let’s increase our grid now we always want more and more and more right now let’s say that we want to generate unique customer id so we have generated unique order numbers all fine we have generate order number as per vendor number that’s works fine now i would like to go and generate unique numbers as per customer so in other words you can see here rajendra and rajendra i would like to go and generate the unique number for rajendra because so then i can identify that this is the Rajendra sukesh, Raju Shiv so i would like to go and generate unique numbers for each one of my customer so what i can do is i first go and copy past this ctrl+c or ctrl+v so i would like to go and generate unique numbers on the customer so this is my customer number first what i will do is let me just go and fir the row number row number function let’s see what happens now if you just use the row number function you can see that he has given a unique number over here but you can see that for Rajendra he has created two unique numbers here 1 and 2 i don’t want that what i want is for Rajendra it should be only 1 ok let me repeat if i just use the row number over the customer name you can see that it just give unique numbers to the row ok in other words even if Rajendra repeats he does not care he is just gives 1 for Rajendra 2 for Rajendra i don’t want that i want same customer number to be allocated to the same customer name and that’s where we have the Rank functions now if i use the rank here absolutely the same syntax you are just go and write rank here means living the row number you have to just go and replace it with Rank now if you execute this now watch here now you can see here 1 for Rajendra you can see there again 1 for Rajendra that is right so wherever i use customer number 1 it will actually pick up Rajendra 5 for Sukesh 3 for Raju 4 for Shiv now this 5, 3, 4 why it is come like this because our orders are the order by clause is not proper here you know the order by clause bit happy get this is not coming in a proper order but that is irrespective now but you can still see that every customer name is now assign a unique number so in Rank what happens is he again gives the unique numbers but if the data repeats he does not if the same kind of data he does not go and generate a unique numbers in other words for Sukesh is generated unique number Raju is generated unique number for Rajendra he generated the unique number but as soon as he found Rajendra again he did not generate the unique number again so if there are repeated data right the Rank function will use the same number for the repeated data so that was Rank now there is a small problem here specially in Rank you can see over here he has set 1 for Rajendra that is good again 1 for Rajendra that is also good but where is the 2 gone suddenly i have 3 for Raju he has allocated 4 for Shiv 5 for Sukesh i would like him to also use 2 so i would like to say that OK 1 for Rajendra or must be 2 for Raju why he has kept the second value i want to him the second value i want to him to use the values in continues fashion and that’s where we can use something called as the Dens Rank so if you just go and replace this with Dens under score Rank and if i say execute now you can see things are better right so 1 for rajendra good and now he is using continue numbers you can see 2 for Raju so he did not skip a numbers so in Rank what happens is he actually skips continues number depending on how much repeated data we have ok so 2 for Raju 3 for Shiv and 4 for Sukesh So i hope that you have enjoyed this video so in this video we will trying to understand what is Row Number, What is partition and what is Dens Rank and Rank THANK YOU SO MUCH 🙂

100 Comments

Leave a Reply

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