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

No video

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

Рет қаралды 561,893

Күн бұрын

👉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 KZfaq: / 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: gatesmashers2018@gmail.com
#findNthHighestSalary#SQL#DBMS

Пікірлер: 436

@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 11 ай бұрын

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

@sr7821 11 ай бұрын

@@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 11 ай бұрын

yea i was working on highest salary only @@sr7821

@ajaysingh-kd4nt 11 ай бұрын

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

@user-zv2nn4vg9g 11 ай бұрын

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

@Dhirendrakumar-dk1dt 2 жыл бұрын

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 ❤️

@aalirashid7178 4 жыл бұрын

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

@nickbaib1839 4 жыл бұрын

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

@GateSmashers 4 жыл бұрын

wow..Thank you for your support..

@kushagrasahu8289 4 жыл бұрын

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

@ayeshaameen3903 2 жыл бұрын

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

@varshasingh2748 4 жыл бұрын

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

@vinitakeer515 4 жыл бұрын

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

@ishangujarathi10 Жыл бұрын

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

@ayushyadav4180 3 жыл бұрын

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.

@vivekgupta5041 3 жыл бұрын

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

@ShivamSharma-xk1yp 2 жыл бұрын

Right

@nitin9042 2 жыл бұрын

Average netpack at 200 can get you knowledge worth milli6

@ayushnegi5123 2 ай бұрын

😢@@ShivamSharma-xk1yp

@ranjeetpatra9212 3 жыл бұрын

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

@kanaksingh8542 3 жыл бұрын

This is the Query for to find nth Salary: SELECT DISTINCT Salary FROM tblemployee ORDER BY Salary DESC LIMIT 1 OFFSET (n-1) If you want to find 8th highest salary, query should be : SELECT DISTINCT Salary FROM tblemployee ORDER BY Salary DESC LIMIT 1 OFFSET 7 Note: OFFSET starts from 0th position, and hence use N-1 rule here

@MrAmmugadu 4 жыл бұрын

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

@bhuppidhamii Жыл бұрын

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

@nivi6414 4 жыл бұрын

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

@aayush5474 3 жыл бұрын

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

@aashishgoyal1436 2 жыл бұрын

quite helpful comment.

@ankitbaiskhiyar3106 2 жыл бұрын

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

@souviknag 2 жыл бұрын

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!!

@PythonisLove 3 жыл бұрын

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

@zabilmaooz7780 3 жыл бұрын

same here

@anitha6661 3 жыл бұрын

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

@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.

@gotamkartik21 3 жыл бұрын

thanks buddy, now i can understand this concept better.

@yuvrajoberoi7834 4 жыл бұрын

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

@bharadwaj003mydreams 4 жыл бұрын

It's true

@shubhamsood1406 3 жыл бұрын

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 2 жыл бұрын

Thanks buddy it's easily understood

@sayalikawle4273 2 жыл бұрын

your query is not firing giving errors

@subscribeinsteadlike2768 2 жыл бұрын

hyyyy sir jab itna jabardast padha kar thank you bolte ho to mai sharma jaaata huu!

@NishaOnYoutube 3 жыл бұрын

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

@rounak3239 4 жыл бұрын

Gurujee shandar jabardast zindabaad

@ravi6996 3 жыл бұрын

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

@pruthvirajjadhav4049 4 жыл бұрын

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

@OmitA25 4 жыл бұрын

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

@chanchalarya983 2 жыл бұрын

THIS IS CALL GENIOUS WAY TO RIGHT QUERY

@rishabhsen9222 3 жыл бұрын

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

@hudachanna4918 11 ай бұрын

You are amazing. Finally I understand how this works

@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.

@ravikumar-wj3rr 3 жыл бұрын

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

@drawingloverswithsohana6194 2 жыл бұрын

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

@Ammuworld_himaja 3 жыл бұрын

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

@kedandu4209 3 жыл бұрын

Well explained . I highly appreciate your talent and logic.

@sushantsinha8216 2 жыл бұрын

best channel ever over youtube for understanding sql tutorial

@pradyutpanja5313 2 жыл бұрын

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 2 жыл бұрын

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

@rahulmungali7810 Жыл бұрын

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

@Arsaliqbal 3 жыл бұрын

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.

@surajkolekar7944 3 жыл бұрын

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.............

@misunadiyan 4 жыл бұрын

Great personality

@kritisk1 4 жыл бұрын

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

@avishgoswami2141 3 жыл бұрын

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

@Aritra20 Жыл бұрын

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

@kunalsingh5794 4 жыл бұрын

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

@harshjilka 3 жыл бұрын

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

@iCyrus 3 жыл бұрын

😅🤣

@jovitalobo1273 Жыл бұрын

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

@shadabsiddiqui28 Жыл бұрын

sir apki jinti tareef ki jaye kam hai you are great

@Diversifiedlady 3 жыл бұрын

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

@LV-ni6tf 2 жыл бұрын

Urs explaining way really super....

@LV-ni6tf 2 жыл бұрын

@shrikrushnakarkhele9122 Жыл бұрын

superb info sir

@KumarAnalytic 3 жыл бұрын

*सर मैं चाहता हूं कि मैं यूट्यूब के जैसा ही अपना प्रोजेक्ट बनाना चाहता हूं डाटा स्टोरेज १० या २० TB से शुरू करना चाहता हूं। मै आपसे से विस्तृत जानकारी चाहूंगा कि कितना खर्च आएगा हार्डवेयर की cost मैं उम्मीद करता हूं कि आप इस पर वीडियो बनाएंगे, मै जानना चाहता हूं कि क्या यह प्रोजेक्ट मेरे बस की है या नहीं, आप का बहुत बहुत आभार!!?* ⛳⛳⛳⛳⛳⛳

@nishu2166 2 жыл бұрын

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

@deekshakhan4506 2 жыл бұрын

such a nice concept i got from u sir🥰

@lunatic792 3 жыл бұрын

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

@yasmeenkarachiwala9612 4 жыл бұрын

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

@GateSmashers 4 жыл бұрын

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

@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);

@AayushJain-mr6bx 4 жыл бұрын

Great playlist for DBMS. Very well explained. Kudos.

@GateSmashers 4 жыл бұрын

Glad it was helpful!

@syedali9824 4 ай бұрын

to find nth hight salary, using this query.......... using emp table. select e1.sal,count(distinct e2.sal) from emp e1 join emp e2 on e1.sal

@pavangsk8404 Жыл бұрын

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

@shrutigundgal415 3 жыл бұрын

thank u for explaination..but have one doubt. while calculating 4th highest salary why it will give id as 2 and why not 3 as id = 3 also have 4th highest salary.

@divyanshpatel1301 3 жыл бұрын

Do u get the answer?

@ArBond007 2 жыл бұрын

@@divyanshpatel1301 Both of the id's will be printed

@vivekpalle3403 11 ай бұрын

Another method: select top 1 salary from (select top n * from employee order by salary desc) as employee order by salary asc If want nth lowest, then just shuffle asc with desc and desc with asc in the above query.

@Poorna10088 3 жыл бұрын

Your explanation is awesome bro

@jayasreecarey7843 2 жыл бұрын

Thanks ! Nice Explanation :)

@fatimaiqra2169 2 жыл бұрын

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

@shobhitgupta3885 3 жыл бұрын

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.

@kumarsaheb Жыл бұрын

best explanation ever.. God Bless !!

@geetbawa990 3 жыл бұрын

Excellent.... God bless u sir

@01kumarr 2 жыл бұрын

Superb much better than a book

@deepa8177 2 жыл бұрын

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

@m.tayyab871 2 жыл бұрын

really found quality content...

@learner_1228 3 жыл бұрын

explanation- Intuition/idea- Let us assume total distinct salaries are N. -What nth highest salary denotes? nth highest salary means that there exist (n-1) salaries which are strictly greater than nth highest salary. - therefore we need to find salary satisfying above condition. -How to do it? Take salaries one by one and compare it with other salaries and count salaries greater than current salary. It's like brute force. if count is equal to n-1. stop checking and return ans. -If we observe it carefully ,it is like 2 FOR loops(C++,C,JAVA), in which outer loop value is compared with inner loop variable. This can be achieved in SQL By CORRELATED NESTED QUERY.

@zabilmaooz7780 3 жыл бұрын

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

@AyurshiK 2 жыл бұрын

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

@anujgrewal4456 4 жыл бұрын

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

@neelesh621 3 жыл бұрын

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

@entrtnmnthub5357 3 жыл бұрын

sir ur teaching is jst WOW!!

@avaniagarwal8973 4 жыл бұрын

Really amazing explanation!!!

@ranatalha3118 5 ай бұрын

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

@user-et5tp9gb3s Жыл бұрын

Those who got confused, can use this SELECT MAX(salary) AS second_highest_salary FROM emp WHERE salary < (SELECT MAX(salary) FROM emp);

@shahidzaman1831 Жыл бұрын

Please explain your full query... And how you write second_highest_ salary... How you write underscore.. What it represents.

@viral_video_ayana 3 жыл бұрын

Thankyou sir

@pawan8154 2 жыл бұрын

really appreciable...

@santoshsomu 10 ай бұрын

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

@suyash7450 Жыл бұрын

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

@divyanshchaudhary7063 11 ай бұрын

Select * from (SELECT * FROM tbl_orders order by amount desc limit 3) as CUS order by amount asc limit 1;

@shashanksharma7747 3 жыл бұрын

very good explanation underrated channel deserve more subscribers

@shreengul6488 3 жыл бұрын

Greetings from Pakistan! I have no words to describe how good you explain every concept sir. Keep it up👍🏻

@waqaskhan2165 3 жыл бұрын

Yay log na hotay to hum pakistanion ka kia huta hahaha

@karanmalkhede6689 2 жыл бұрын

@@waqaskhan2165 hihi pakistan ka bhi ek yt hai mohammd naam se wo bhi mast sikhate sql .maine ek 2 topic unse hi sikhe hai

@neelkanthbk 4 жыл бұрын

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

@VickySharma-rz7he 2 жыл бұрын

Great Explanation Sir thanks for such work

@ashvinimeshram5242 3 жыл бұрын

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

@sandhyajain7472 3 жыл бұрын

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.