welcome to presume technologies in this video series we'll discuss the most commonly asked sequel server interview questions and answers if you have attended an interview and if you have a question to be answered please feel free to leave that question as a comment and I shall record and upload a video answering that question as soon as I can with that let's dive in in this video we'll discuss how to find the nth highest salary in sequel server there's one of the very commonly are sequences of an interview question and keep in mind there are several ways of finding the nth highest salary in this video we'll discuss a few of those by the end of this video we'll be able to answer all these questions how to find the nth highest salary in sequel server using a sub query how to find the nth higher salary using a CTE how to find the second third fifth or even 15 the highest salary for the purpose of this demo we'll be using this employee's table the secrets clip to create disable I'll have it available on my blog in case you need it now if somebody asks us to find the higher salary that's straightforward all we do is use the max function let's flip to sequel management studio so at the moment this data is already sorted by salary in descending order so the higher salary is 8,000 so if we want the highest salary they simply say select max off salary from the employees table so that should give us 8,000 but if somebody asks us to retrieve the second higher salary then how are we going to do that so this how we'll do it so we want salary from employees their salary is less than whatever is the higher salary so here we are making this a sub query ok so now if we execute this what do you think are we going to get so we are getting every salary you know which is less than the higher salary but that's not what we want we want the second highest salary so to get the second highest salary we are going to use the max function with the outer query as well so now when we execute this we should get the second highest salary which is seven thousand so let's execute both of this query so we can see the result side by side so here if you notice the second highest salary is seven thousand that's what we have got so this is straightforward query okay now let's see how to get nth higher salary first let's see how to get nth higher salary using a sub query so let's flip to sequel server management studio so first I'm going to write you know the sub query itself so select distinct top you know whatever salaries let's say for example we want to get the second highest salary so select top to salary from which table from employees table and we are going to order let us sort the rules by salary in descending order now look at this when we execute this query so we get the top two highest salaries let's execute both of them so that the highest salary is 8,000 second highest is 7000 so we are getting the first highest and second highest that's because we have used top two here and they are in descending order now we are going to make this a sub query so let's put that within a pair of brackets and let's give this a name let's call it resolved okay now let's write the outer query so we want to select salary from this result set so when we execute this query as you might expect we are going to get both the rows now what we want is we want you know the second highest salary and if you notice the result here look at this they are in descending order so first of all let's sort the results in ascending order so for that I'm simply going to use order by salary okay so when we execute this query what's going to happen you know the sorting is going to be reversed so 7,000 and 8,000 and out of these we want just the first row so what we're going to do here select top one salary from that result set so when we execute this look at that we only get the second highest salary one record now let's actually execute both of them so now this query can be used to retrieve any of the higher salary if you want let's say third higher salary so what is the third highest salary here first is 8,000 second is 7000 third is 6,000 so if you want the third higher salary simply replace this with three so when we execute this now we should get 6,000 as the output look at that okay so you can use this query to find any of the nth highest salary and here we have seen how to do it using a sub-query now let's see how to do it using a CTE CTE stands for common table expression so let's flip the sequel server management studio so select now we are going to select salary and I'm going to use dense rank sequel server function here in a bit you will understand what dense rank function is going to do now it's going to obviously rank rows and then give in a rank to every row so when we use dense rank function we'll have to specify the result set upon which we want that dense rank to be applied so we want the dense rank to be applied over you know the result it said that we are going to specify here so what we want to do is we want to order by salary column and that in descending order okay so on this result set you know apply a rank okay in a bit you'll understand what I mean by that so from what table employees table now let's give this you know whatever you know this expression is going to return you know that column a name let's call this maybe dense rank okay so let's execute this and see what output we get so if you notice in a look at that that's Gallery and that's the ranked which the Stensland function has given now the highest salary is 8,000 so it's giving that rank number one and 7,000 you know we have two rules the second highest salary so notice that rank for both of the rows is - okay so dense rank that's what it does it gives rank based on the values and we have specified here we want to you know sort the salaries in descending order and then apply the line for them so obviously here two rows are the same which means they get the second rank and this fourth row which has got six thousand will get the third rank again look at this after that we have two 4,500 so which means you know fourth rank is applied for both of the rows and fifth rank goes to 3,500 I hope dense rank function is clear okay so now what we're going to do we are going to make this a common table expression and the way we do that is by using with key word and let's call this common table expression result and give it a name as so this is of a common table expression so now as the name States this is a common table expression you can use it as a table so what I'm going to do is select what we want from the common table we want salary column from what is the name of the CDE it is result so from result where let's say we want the second highest salary so if you look at you know this result set what is the other column name it's called dense rank and that's going to be present within this result set so from result where result dot dense rank equals now if we want the second highest salary I put two there so now when we execute this we should get 7,000 which is our second highest salary but notice that here I get two rows now if you don't want both the rows simply use the top class here so select top one salary so now if we execute this we should get only one row so we get the top one salary there so the second highest salary now if you want the third-highest celery you simply put three there and execute this query and notice that we get the third higher salary and what is third higher salary at 6000 let's actually include it go there so now if we execute this look at this third highest salary is 6000 okay all right so we have seen how to retrieve you know entire cells using a sub-query and using a CTE as well now obviously you know this is going to be n here meaning if you want 15 tires every simply replace it with 15 and it's simply going to work so you can do that either you know with this query or this query okay so if an interview ask you to find any of the highest salary you know you know what to do now it's not just celery it could be any numerical number in that case okay now in most of the blogs on the internet you see that this query can actually give us the nth highest salary but actually there's something wrong with this query this query is going to work fine if there are no duplicates but if there are duplicates this is not going to work as expected let me explain what I mean so here they're using row number function so let's actually flip to the slide so instead of using dense rank you know they're using row number function so when we use row number let's also give this Ali as a meaningful name so let's call this row number okay now when we execute this query let's say we want third highest salary okay let's actually try second highest salary first let's execute both of these queries so now look at this what is the second highest salary 7,000 so we get that correctly but what is the third highest salary here third highest salary is 6,000 right so now if I want third highest health if you put three there and then execute this one what are we getting we are getting 7,000 that's wrong and why is that happening that's basically happening because of this phone number function here this roll number function it's going to give sequential row numbers for every row so 7,000 has got row number 1 2 3 you know 7000 has got row number 2 and this 7000 has got row number 3 so when we say result dot row number equals 3 we are going to get this salary so that's why you know we get the wrong result so if you use row number you know this is going to work if the table doesn't contain duplicate data but if it does contain duplicate data then it's not going to work that's why it's better to use dense rank function all right that's it for today thank you for listening have a great day
