Lec-70: Find Nth(1st,2nd,3rd....N) Highest Salary in SQL | Imp for Competitive & Placement exam

👉Subscribe to our new channel: / @varunainashots
► Structured Query Language (SQL)(Complete Playlist):
• Structured Query Langu...
Other subject-wise playlist Links:
--------------------------------------------------------------------------------------------------------------------------------------
►Design and Analysis of algorithms (DAA):
• Design and Analysis of...
►Computer Architecture (Complete Playlist):
• Computer Organization ...
► Theory of Computation
• TOC(Theory of Computat...
►Artificial Intelligence:
• Artificial Intelligenc...
►Computer Networks (Complete Playlist):
• Computer Networks (Com...
►Operating System:
• Operating System (Comp...
►Database Management System(Complete Playlist):
• DBMS (Database Managem...
►Discrete Mathematics:
• Discrete Mathematics
►Compiler Design:
• Compiler Design (Compl...
►Number System:
• Number system
►Cloud Computing & BIG Data:
• Cloud Computing & BIG ...
►Software Engineering:
• Software Engineering
►Data Structure:
• Data Structure
►Graph Theory:
• Graph Theory
►Programming in C:
• C Programming
►Digital Logic:
• Digital Logic (Complet...
---------------------------------------------------------------------------------------------------------------------------------------
Our social media Links:
► Subscribe to us on TH-cam: / gatesmashers
►Subscribe to our new channel: / @varunainashots
► Like our page on Facebook: / gatesmashers
► Follow us on Instagram: / gate.smashers
► Follow us on Instagram: / varunainashots
► Follow us on Telegram: t.me/gatesmash...
► Follow us on Threads: www.threads.ne...
--------------------------------------------------------------------------------------------------------------------------------------
►For Any Query, Suggestion or notes contribution:
Email us at: [e-mail]
#findNthHighestSalary#SQL#DBMS

[@aalirashid7178]

GREAT EXPLANATION SIR IN WHOLE TH-cam DATABASE TUTORIALS I FEEL COMFORT WITH YOUR TUTORIALS ONLY STAY BLESSED LIKE SAME FROM PAKISTAN..

[@Dhirendrakumar-dk1dt]

Make it very easy to understand SQL. I have watched your all series of SQL and that helps me a lot to crack my interview, thank you very much sir for sharing your information ❤️

[@nickbaib1839]

Thank you so much!
Continue the good job my friend!
Greetings from Greece!

[@GateSmashers]

wow..Thank you for your support..

[@kushagrasahu8289]

Your channel made SQL learning easy informative and fun🙌.
I literally binge watched sql series.

[@ayeshaameen3903]

Wowwww You are a genius. No one have ever taught me like this..
thank you so much for this one specially.

[@ayushyadav4180]

i think in this world there would be no teacher other than him who don't need his students to know the language he is talking in i have seen comment of ppl from all over the world appreciating him.

[@vinitakeer515]

Thank you sir jb ap bolte hai k ye meri garrenti hai that puch line is superb.... God bless you

[@varshasingh2748]

Aapka lecture dekhe bina din achha ni jata your enthusiastic way of teaching fill Josh.

[@ishangujarathi10]

One of the frequently asked question in interviews and placement/internships tests, thank you so such a perfect and easy explanation!!!

[@rishabhsen9222]

I have a doubt...
Inner query me to condition ye hai ki ' e2.salary > e1.salary '... To pehle e2 ki salary 10,000 ka comparison e1 ki salary k sath hoga to first comparison me to answer 0 hi ayega kyoki 10,000 e2 ki kic bhi salary se bda nhi hai... Jbki sir ne ulta comparison krke pehle comparison me answer 4 nikal diya... Sir ne ' e1.salary

[@ranjeetpatra9212]

The way you elucidate this complex thing is really appreciable. Hats off ❤️

[@annoniimous]

Alternate Answer for finding nth highest salary:
SELECT Salary FROM employee
ORDER BY Salary Desc
limit 1 offset 0
Here,
limit means : how many output values you want to show on the screen ()
offset means: let assume you want to show nth (4th) highest salary . In this case you will provide offset 3 which means you
are leaving top 3 rows and you are choosing the 4th one . (( "offset means escaping the number of rows from top"))

[@ajaysingh-kd4nt]

limit does not work with oracle sql
select * from (select * from employee order by salare desc) where rownum

[@sr7821]

@@ajaysingh-kd4nt your code selects highest salary not Nth. I think this should work:
select * from (select * from (select * from employee order by salary desc) where rownum

[@ajaysingh-kd4nt]

yea i was working on highest salary only
@@sr7821

[@ajaysingh-kd4nt]

yes that might work filtering the Nth tuples and then getting the record
@@sr7821

[@AMANPATEL-g8v]

mnnit wle bhi aa rhe kyaa , biotech me kb se dbms aa gya bhai ??

[@shubhamsood1406]

Another approach for MySQL users, also worked on duplicates salaries
SELECT * from Employee WHERE salary= (SELECT DISTINCT salary from Employee ORDER BY salary DESC LIMIT N-1, 1);

[@sanyogsalve6256]

Thanks buddy it's easily understood

[@sayalikawle4273]

your query is not firing giving errors

[@vivekgupta5041]

No need of costly books !!! Much more matter than that provided by books !!!

[@ShivamSharma-xk1yp]

Right

[@nitin9042]

Average netpack at 200 can get you knowledge worth milli6

[@ayushnegi5123]

😢​@@ShivamSharma-xk1yp

[@ravi6996]

We can use LIMIT, OFFSET for yielding same results in case of finding nth highest

[@bhuppidhamii]

how does he explains every topic in a clear and comprehensible manner.😭💖

[@surajkolekar7944]

select a_id,salary from agg as a1 where 3 = (select count(distinct(salary)) from agg as a2 where a2.salary > a1.salary);
agg is table name..
Thnk u for simplification.............

[@MrAmmugadu]

Thank you so much sir, before I watched your video I was totally frustrated mind, bcoz I unable to understand subqueries,but after watching your vedio really I got more analysis points on subqueries, thank you again sir

[@NishaOnYoutube]

Sir the way you teach and explain is beyond amazing...because of you I got golden badge in hackerrank....

[@souviknag]

I'm watching it very late. And there's only one thing that's going on in my mind, ITS BETTER LATE THAN NEVER!! I'm so glad that I found this channel after all. Subscribed!!

[@krishna9971]

Thank you sir, i was searching this concept from long ago, finally understood the working from your video only. Thank you for making sql easy.

[@PythonisLove]

you cant believe, I can solve most dbms questions already after taking notes from your videos...I have never read a book

[@zabilmaooz7780]

same here

[@anitha6661]

Thank you so much for this video. I was struggling to understand the logic behind this and you just cleared all my doubts!!! : )

[@Iamabhay_]

This is very simple.......
Select distinct salary from employee order by salary desc limit n-1, 1
Here n is the nth highest. salary.

[@nivi6414]

Thanks a lot sir!. Been looking for the perfect explanation for months. Thanks again!

[@yuvrajoberoi7834]

No one has helped me more learning SQL queries than you sir.

[@bharadwaj003mydreams]

It's true

[@ankitbaiskhiyar3106]

select max(salary) from Employee e1 where N-1 = (select count(distinct salary) from Employee e2 where e2.slaray > e1.salary (Leetcode)

[@drawingloverswithsohana6194]

I like your all vedio and always watch your vedio when I fetch trouble to solve CSE DEPARTMENT related problem.

[@OmitA25]

Wow sir....its a great thing to have you on youtube....very informative video....thanku so much....

[@gotamkartik21]

thanks buddy, now i can understand this concept better.

[@kedandu4209]

Well explained . I highly appreciate your talent and logic.

[@kunalsingh5794]

Sir please also upload videos on digital electronic...
....... Please make video on sequential circuit....🙏🙏🙏🙏

[@pradyutpanja5313]

Thanks sir , last day in my interview 2nd highest salary was asked to me .my answer was right like....SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee).. but i had a huge confusion on like how to find out the other cases 4th ,5th etc. but all confusions are totally cleared now. Thanks a lot sir.. now i have decided to watch your all videos related to SQL. and also i will recomend this to others who are actually facing on sql. such a nice tutorials...

[@smrutiranjanparida9766]

see no one answering while everyone busy praising channel,

[@avtarchandra2407]

abb samjh me aya sirr ki Nth salary ku famous question hai ...kuki isme pura basic database grind ho jata hai ...love your effort

[@rounak3239]

Gurujee shandar jabardast zindabaad

[@pruthvirajjadhav4049]

This logic is such that
how many kids are elder than this child if there two child greater than this one then he is third .
how to find the the second last child ?
Q . how to find the second minimum salary ?
ANS : try to get this .
select salary ,id
from emp e1
where n-1 = (select salary count (distinct salary)
from emp e2
where e2.salary

[@Arsaliqbal]

use distinct with sal in outer query to solve duplicate issue. (For 4th highest salary)
select distinct salary
from Emp e1
where 3= (
select count(distinct salary)
from Emp e2
where e2.salary > e1.salary
)

[@SantoshYadav-hj8kv]

@Arsaliqbal bro i am still geeting a duplicate salary.

[@Ammuworld_himaja]

Thankyou very much, this is much helpful. Now i understood why that N-1 concept works just with the COUNT.

[@shobhitgupta3885]

Something like Select Min(Salary) from Emp AS e1 where N - 1 >= (Select Count(Salary) from Emp AS e2 where e2.Salary > e1.Salary) could also work.
If 5000, 4000, 30000, 20000 ,20000, 10000 is expected order of salaries.

[@hudachanna4918]

You are amazing. Finally I understand how this works

[@sushantsinha8216]

best channel ever over youtube for understanding sql tutorial

[@rahulmungali7810]

One of the best video available in internet for correlated query👍👍

[@ravikumar-wj3rr]

you are great sir very best way to teach thank you sir

[@AayushJain-mr6bx]

Great playlist for DBMS. Very well explained. Kudos.

[@GateSmashers]

Glad it was helpful!

[@shadabsiddiqui28]

sir apki jinti tareef ki jaye kam hai
you are great

[@nishu2166]

Excellent sir....
Really logic is very imp and that is clear... 👌👌💐

[@jayasreecarey7843]

Thanks ! Nice Explanation :)

[@ranatalha3118]

Sir kindly check that ,Might be its easy query from your method.Please share your opinion.SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 4;
If we want 5th highest salary

[@BEKKAMBHARATHASIMHAREDDY]

BEST EXPLAINATION EVER AND EVER SIR
THANK YOU SO MUCH
🥰🥰🥰🥰

[@fatimaiqra2169]

Mny b try kiya bilkul thik ans aya ...best explanation sir

[@sandhyajain7472]

Sir can you dive in a little more and explain why we did E2.salary > E1.salary not the other way around. Thanks for all the explanation that does not go over my head.

[@lunatic792]

Bhai behre insaan ko bhi samjha sakte hein, thank u so much.

[@aayush5474]

Take Away: Inorder to find Nth highest salary the salary should have N-1 salaries which are greater than given salary

[@aashishgoyal1436]

quite helpful comment.

[@Diversifiedlady]

There are two tables salesman and customer.the question is - write a query to find those customers with their name and those salesman with their name and city who lives in the same city............ common attribute is salesman_id and city

[@kritisk1]

Thanks a ton, one of the best video which clearly explains the concept and logic behind the query.Im from Tamil Nadu but I was able to follow the language without any trouble because of the explanation

[@yasmeenkarachiwala9612]

Thank you so much Sir!
Clear, concise teaching, making sure the fundamentals are understood !
Greetings from San Francisco!

[@GateSmashers]

thank you so much..its a great feeling that people from US appreciate my work. Thank you for your love and support..bless you

[@Aritra20]

select id, name, salary from emp e1 where N-1 = (select count(distinct(salary)) from emp e2 where e1.salary < e2.salary);

[@pavangsk8404]

kutha bhi dhekega tho samaj lega sir thank you so much... wonderful explanation

[@pradeepkumar-qu1gc]

Very nice explanation friend. thanks a lot

[@avishgoswami2141]

Man you nailed it...Hats off to you!!!

[@AyurshiK]

great work sir beautifully explained difficult topics. it helped me a lot.

[@manishKumar-tl2ee]

We can get the same using this Query also,
SELECT DISTINCT salary AS nth_highest_salary FROM emp ORDER BY salary DESC LIMIT 1 OFFSET (N-1);

[@chanchalarya983]

THIS IS CALL GENIOUS WAY TO RIGHT QUERY

[@neelesh621]

Was seraching this..thnk u sir for this awesome level explanantion!!

[@01kumarr]

Superb much better than a book

[@jovitalobo1273]

you explained this complex theory in awesome way. hats off to your efforts

[@deepa8177]

thanku for explaining each line got so much crystal clarity..

[@deekshakhan4506]

such a nice concept i got from u sir🥰

[@ashvinimeshram5242]

Thanks sir. This is very helpful. Now I am able to write quaries.🙏🙏🙏🙏

[@LV-ni6tf]

Urs explaining way really super....

[@LV-ni6tf]

[@shekharsalunke226]

Hi Sir,
Thanksgiving your knowledge.. this method is too good.. 👍

[@md.anisurrahman1723]

Nice explanation, to speak the truth.

[@VickySharma-rz7he]

Great Explanation Sir thanks for such work

[@harshjilka]

Compiler confuse ho jayega : ye kya bat bani 😎🔥😂 OP

[@iCyrus]

😅🤣

[@shrikrushnakarkhele9122]

superb info sir

[@sagarbhavsar7774]

very useful

[@anujgrewal4456]

Very nice way Sir .......today i cleared this way ........

[@Poorna10088]

Your explanation is awesome bro

[@kumarsaheb]

best explanation ever.. God Bless !!

[@mohammadrizwan3779]

Sir your video is excellent no 1 do one more thing if you give example on sql server management studio that would be much better than board.

[@shashanksharma7747]

very good explanation underrated channel deserve more subscribers

[@subhenduchsingh4981]

Really Appreciate ... Have understood the concert 👍👍

[@zabilmaooz7780]

my solution:
select * from employee where salary = (select min(salary) from (select salary from employee order by salary desc limit 41) as salary_table);

[@m.tayyab871]

really found quality content...

[@misunadiyan]

Great personality

[@geetbawa990]

Excellent.... God bless u sir

[@hireinnag2939]

Great video. Very much explanatory!

[@narenderreddyvanga8908]

Excellent Explanation..👌

[@digantaboro5227]

Sir Error control ke baare me briefly video banayi ga please ?
Sir why timer is necessary in Error control? Does their is possible to lost of header and flag ? If yes then whole data is corrupted.
I'm confused

[@neelkanthbk]

Thank you so much, for explaining it such simplicity!!! You made it so easy to understand!!

[@santoshsomu]

thank you bro, i was able to understand easily with your detailed and examples explanation, thank you !

[@renukarani7392]

Great explanation

[@thesportslightmaker5988]

sir gate ka previous year questions bhi solve krro plzz

[@mohammadnazim4515]

Excellent sir, very easy to understand

[@sakijilan7908]

I have to say Sir ji, You are simply the BEST!!

[@jitendraverma3783]

Great concept
Learned something new
Thanks 👍

[@nehadubey7192]

Nice..thanku for guidance

[@suyash7450]

yOU make it very easy to understand......

[@sumenbhattacharyajee7091]

Very Nicely you explained. 🙏

[@anupkumarsahoo4506]

Thank You Sir, It's just a great Explanation.