fetch

Part 1 How to find nth highest salary in sql



Sharing buttons:

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