馃攳
Lec-66: Find Nth(1st,2nd,3rd....N) Highest Salary in SQL | Imp for Competitive & Placement exam - YouTube
Channel: Gate Smashers
[0]
Hello friends!
[0]
Welcome you to Gate Smashers
[2]
In this video we are going to discuss
[4]
SQL query
[5]
And what is the query
[6]
Find the N th highest salary
[8]
Over here the value of N you can take 1,2,3 upto N
[12]
If we talk with placement point of view
[14]
Or we talk with the competitive exam point of view
[17]
Then SQL
[18]
Is one of the most important topic
[21]
And specially with the point of placement
[23]
It is very important
[24]
It is the first priority that
[26]
We ask some query from SQL
[29]
Now obviously in SQL there are lot of queries
[31]
But, in that also if we talk
[34]
Find the highest salary
[36]
First highest
[37]
Means if we want to find the top
[39]
Or 2nd highest
[40]
3rd highest
[41]
4th highest
[42]
This is very frequently asked question
[45]
That one table is given to you
[46]
Write a query to find the 5th highest
[49]
10th highest
[50]
So how you have to do this type of query
[52]
Because many students get confused in this
[55]
Single query, let's say I am given any table
[58]
If let's say we have Emp table
[60]
In Emp table
[62]
Let's say
[63]
There is a Eid and Employee salary
[67]
I am taking just 2 columns
[68]
Because we have to main find out the salary
[71]
So I am taking Id and Salary 2 columns
[73]
You can take name anything else address, phone number anything else you can take
[77]
Let's say Id is 1
[79]
Salary is 10000
[80]
2 salary is 20000
[83]
3 salary is let's say of him also 20000
[87]
4 30000
[89]
5 40000
[92]
6 50000
[95]
I have taken over here 6 employees data
[98]
10,20,20,30,40,50
[100]
I have taken these salaries
[101]
Over here I have repeated the salary
[103]
Because any times what happens that
[105]
We have to take all type of data in the table
[108]
It is not so
[109]
That you take a fix type of data
[111]
And write query on it
[112]
If you get the answer
[113]
It is not necessary that
[114]
That answer can be in some data
[117]
It will be wrong
[118]
So you have to take this type data where all the cases are covered
[122]
Means you have to write query in such a way
[124]
That no matter whatever is the data
[125]
Your query will give exact answer over there
[128]
So I have taken this employee table
[130]
Now in this employee table
[131]
If I want to find out the highest salary
[134]
Then there is a simple funda with us
[135]
Select max(salary)
[140]
We have an aggregate function
[142]
Select max(salary) from Emp
[144]
Let's say table name is Emp
[146]
So I have done select max(salary) from Emp
[149]
Over here you will get 50000 answer
[151]
Because max will be default find the maximum value
[154]
But if I want to find second highest then what will I do
[158]
Select max(salary) from Emp where, write with this only
[162]
Where salary Not In (select max(salary) from Emp)
[175]
What does this mean
[176]
Over here we have written nested query
[177]
And in this nested query
[179]
Internally select max(salary) from Emp will work
[182]
What is select max(salary) from Emp?
[184]
50000 is the answer
[186]
Over here the answer is 50000 of this
[189]
Now what is there in the outer query
[190]
Select max(salary) from employee where salary Not In
[193]
What is the meaning of Not In that 50000 will not be there
[196]
So which are the salary?
[197]
10,20,20,30,40
[201]
Now from this also what I have to find max(salary)
[203]
So obviously my answer will be 40000
[206]
Will be my answer
[207]
So over here I have got the second highest
[209]
Now after this the third highest
[211]
4th highest
[213]
5th highest
[214]
Over here the students get confused
[215]
So over here I am bringing a query
[217]
And I am giving you multiple methods
[219]
But this method is absolutely full proof
[221]
And you will always remember
[223]
The method is very simple
[224]
Select id,salary whichever attribute you want to find
[228]
From emp
[230]
This table
[231]
We have generated an Aliases e1
[233]
We have made a dummy name e1
[236]
Where N-1 what is N over here?
[239]
Nth
[240]
The highest salary that you have to find
[242]
Let's say
[243]
You have to find 5th highest then put 5 over here
[245]
Is equal to
[246]
select count( distinct salary)
[248]
We have to find count over here
[250]
Of distinct salaries
[251]
From emp
[252]
e2
[253]
We have made one more Aliases e2
[255]
Where (e2.salary is greater than e1.salary)
[260]
So over here
[261]
We have written correlated nested query
[263]
How do we recognise correlated nested query
[266]
Where outer queries
[268]
Some value we have used in inner
[270]
See the employee table in outer query
[272]
We have made Aliases e1
[274]
And e1 we have used internally in this query
[279]
We call it correlated nested query
[281]
And I have already made video regarding correlated nested query
[285]
And its link I have given in the description box
[288]
In that I have told in detail
[289]
What is correlated nested query
[291]
With example
[292]
So one time you definitely watch that video
[293]
Over here we are having main focus
[295]
On Nth highest salary
[297]
Now let's say
[298]
I want to execute this query
[302]
Now we have to see how to run this query
[304]
That is more important
[305]
Many times what do students do,
[307]
They cram
[308]
There is no benefit to cram
[309]
You understand its logic
[311]
If once logic is clear
[313]
Then you can write any query
[314]
This is my guarantee
[315]
So over here
[316]
We have made two aliese of Emp table
[319]
This is my aliece
[320]
e1
[321]
Dummy name
[321]
And one we have made e2
[323]
So e2 is that only, but let's say we are copying it one more time
[327]
Salary
[329]
Copy it as it is
[332]
1 10000
[333]
2 20000
[335]
2 sorry 3 20000
[339]
4 30000
[341]
5 40000
[344]
6 50000
[346]
so why do we basically use aliases
[349]
Because we have used employee 2 times
[351]
one over here and one over here
[352]
And if you will not create e1 and e2
[354]
Then over here what you will write?
[356]
Where emp> emp. salary
[359]
emp.salary>emp.salary
[361]
Compiler will be confused
[363]
Which will compile this query
[364]
That what is this
[365]
emp.salary>emp.salary
[367]
Which employees salary is greater then which employee
[369]
So it is better that we have made alias over here
[371]
e1 and e2
[373]
Now over here we will see how to evaluate this query
[375]
So how does correlated nested query gets evaluated
[378]
Outer query
[379]
It is top to down
[380]
First 1 line of outer query
[383]
Which is the outer query
[384]
Select id salary from Emp e1
[387]
Where is e1, it is here
[389]
So its first row
[390]
first row
[391]
Is to be compared with all the rows
[394]
With all the inner rows
[396]
And in inner which table do I have?
[399]
e2
[399]
Now we have to compare e2 with this row
[402]
So what is the comparison
[404]
Where e2.salary >e1.salary
[408]
See over here e2.salary
[410]
This is the e2 salary
[411]
e2.salary> e1.salary
[415]
where is e1.salary? see here 10
[416]
First row is to be compared with all
[419]
So this the first row
[421]
So let's say
[421]
10>10?
[424]
10,10,10,10,10
[428]
See this I have compared with all
[429]
I have not written 10000 over here
[430]
Now simply take 10000 over here
[432]
Now over here we have to find
[434]
It is greater from how many values
[435]
We have to find its count
[437]
But remember distinct with count
[439]
Because over here I have taken some salaries duplicate over here
[443]
If you take unique
[445]
Then this is best
[446]
It will go normally
[447]
But even if salary is duplicate
[449]
Then also this query will run exactly
[452]
What you have to do
[453]
Count them
[454]
What is to be counted?
[455]
10>10
[456]
Is 10000?10000
[458]
No it is not
[459]
Is 20>10? yes
[462]
So what is the count 1
[463]
Is 20>10? yes
[465]
What is the count? 2
[466]
No how much is the count? 1 only
[469]
Because we have taken distinct salary
[471]
So distinct salary in e2 means
[474]
It is to be counted once only
[476]
Is 20>10? Yes
[477]
20>10? yes
[479]
But distinct means it is to be counted only once
[482]
So 20>10 will be one time
[485]
So what is the count 1
[486]
Is 30>10? 2
[488]
40>10? 3
[491]
50>10? 4
[493]
So in this way you have to count
[495]
We counted this 0 came
[497]
Means this is false
[498]
This is true
[499]
But you have to count this once only
[501]
Because this is unique
[502]
Over here we have taken distinct salary
[504]
So 20 is distinct
[505]
So it will be counted once internally
[507]
1,2 , 3,4
[510]
So you can say over here in 4 cases
[513]
I will write over here, in 4 cases your count is following this property
[520]
It is following this condition
[521]
Now what we have to do
[523]
Our question is lets say
[524]
That I have to find
[526]
4th highest
[527]
We have to find 4th highest
[528]
Now lets say we have to find 4th highest
[530]
What I have to put over here?
[533]
The value of N as 4
[534]
So what is query over here
[535]
Select id,salary from emp e1 where 4
[547]
You have to find 4th highest
[549]
This is my question that find the 4th highest salary
[552]
So what is 4-1
[553]
Where 3 = over here what it is 3
[556]
3=(select
[559]
Ahead everything should be written as it is
[562]
Distinct salary
[563]
This whole will come as it is
[566]
Copy this condition over here
[568]
Now what we have to find
[569]
3 is equal to what
[570]
See over here what has came 4
[571]
When we compare 1st row
[573]
Then the count was 4
[575]
That means this is not to be printed
[577]
If it would have be proper
[579]
Then we would have printed this
[580]
Finish
[581]
What is your answer?
[581]
finish
[582]
But no
[583]
Now come on the next row
[584]
What is the next row
[585]
2 20000
[587]
Now compare 2 20000 with all
[590]
Means what is over here
[591]
Instead of 10 what is there at all places
[594]
20
[595]
So write over here 20
[596]
20,20,20,20,20,20
[600]
Now come on this
[602]
10>20? false
[604]
20>20? false
[606]
I told earlier only it is distinct so count it once only
[609]
30>20
[610]
True 1
[611]
40>20
[613]
2
[614]
50>20
[615]
3
[616]
So what will this return? what is the count according to 20
[622]
3
[623]
Over here you have to count
[624]
So what is count
[625]
3
[625]
Now see
[626]
Where 3=
[629]
So 3=3 over here
[631]
Yes
[631]
3=3 Because the internal query has given count 3
[636]
So as 3=3 means it is true
[638]
As this is true
[640]
Which line you had selected? 2nd one
[642]
What is to be printed id, salary
[645]
So see what you have got
[646]
2 20000 will come in your output
[649]
Where 2 is id
[651]
And what is the salary
[652]
20000
[653]
So you see
[654]
In whole database
[654]
This is the highest
[656]
This is the second highest
[657]
This is the 3rd highest
[658]
4th highest is 20000
[660]
We will find one more
[662]
Let's say
[662]
you have to find 3rd highest
[664]
To find 3rd highest
[667]
You note n values
[667]
To find 3rd highest
[669]
What n value did you put?
[671]
3
[672]
What ever 3rd you want to find is the value of n
[674]
3-1 what will come? 2
[676]
Over here 2 came
[678]
Now come
[679]
According to first row 4 had came
[681]
But you don't have to evaluate again
[682]
According to 2 your count was 3
[684]
So there is no need to calculate more
[686]
Come on next row
[687]
3,20
[689]
so see according to 3,20
[690]
What will be you count?
[691]
It will come 3 only
[693]
So there is no need to count it again
[694]
Next come on 4,30
[696]
Over here what it will come?
[697]
30,30,30
[699]
30,30,30,30,30,30
[703]
Come 10000>30? false
[706]
Greater than false
[708]
False false true true
[711]
What is it for 2 lines
[712]
True
[713]
40 is greater 50 is greater
[714]
The value is 2
[716]
So in this case what is your answer
[718]
2
[719]
Means what is the count over here
[720]
2
[721]
So 2=2 matched
[723]
It matched
[724]
So which line was it?
[725]
4th one
[726]
So what came in the output? 4, 3000
[730]
So see which is your 3rd highest
[732]
50 1st
[733]
40 2nd
[734]
30 3rd
[735]
In this way
[736]
30000 salary means your 3rd highest will be printed
[740]
So 3rd, 4th, 10th, 20th
[743]
At present I have just added 3 rows in the database
[745]
Let's say
[747]
Even if they were 6000 rows also
[748]
Then 100th 200th any
[751]
highest salary you can find out
[753]
So this is basically funda
[755]
How do we find out
[756]
Nth highest salary
[757]
So be careful
[759]
We have done with correlated query
[760]
I believe there are multiple methods
[762]
But the best method which you always remember
[765]
Is this method
[766]
So you will a lot with that
[768]
Specially in placements
[770]
And in competitive exams
[772]
Thank you
Most Recent Videos:
You can go back to the homepage right here: Homepage