Query To Find Nth Highest Salary In SQL | SQL Interview Question

[@Indian_mutualfund_expert]

Hi Mam I regularly follow your videos... Mam in subquery at end of the brackets you need to mention any alphabet then only the query will get executed other wise it given error... Select top 1 sal from (select top 3 sal from emo order by sal desc) s order by sal asc...

[@crackconcepts]

Yes. You’re right

[@danishdude6750]

@@crackconcepts And you should do a select distinct top N salary in your inner select so you don't get duplicates if more people have the same salary.

[@AsifIquebalSarkar]

rows can have same salary and we may need to fetch all of them, so WITH TIES would be a better option

[@ashutoshanand4040]

YES I NOTICED THAT AS WELL BUT WHAT IS THE REASON?

[@Somnath-je9nd]

why s outside that bracket ? can you explain

[@akashlondhe1110]

Instead of select top 1 from (subquery) order by sal desc .. you can write select min(sal) from (subquery)

[@maheshtambe9535]

Good one

[@devprakash4671]

Noice

[@kishanmishra7383]

@@chandrakanthotkar7262 Not necessary. It'll give the output

[@karthikgolagani6844]

@@chandrakanthotkar7262 not needed

[@crackconcepts]

Hi, do check out the whole SQL Playlist for more such videos!

[@anerishah679]

Really wonderful explanation!

[@manimuttu1]

Nice explanation!! and please add the playlist url as well :)

[@bharatiyadav8333]

Other clean way is to use limit and offset : "Select sal from Emp Order By sal Desc Limit 1 Offset 2 ;" Limit X Offset Y : select X rows from (Y+1)th row

[@user-pr9pq8en2d]

I had this in my mind..

[@preexa5797]

Hn re limit use krne ka simple and easy

[@sarikadatta3706]

Thank you Bharati Yadav You made it so much easier. I tried your method and it worked. What she showed in video is logical but for some reason it gives me an error in mysql Thanks a ton for helping me out

[@thehavoc6331]

Damn, this is so easy and in one line, but I don't understand how offset works😅 I understood until LIMIT 1 but how does "offset 2" give you the third highest salary Can anyone explain this to me?

[@yogeshwarjoshi]

I think you need a distinct for salary otherwise it will fail if few people have same salaries.

[@aashishprashant2402]

Select T.column_name, Dense_Rank() over (order by salary desc) as Rank from T_Tablename where Rank = N;

[@universal4334]

Geeks for geeks

[@jv___6729]

;with rno as ( Select Id, salary , dense_rank() over (order by salary) as salarycte From emp ) Select Id, salary from rno where salarycte = 3

[@Jyotisingh-qm4fm]

Hello aashis

[@vivekkrishnan514]

Thanks a lot, you just changed my life. I had watched almost all the videos and attended the interview. I passed 1 written exam and 3 technical rounds and I got selected for the SQL Developer Job only because of you. Thanks a lot for the effort you made. Keep teaching us, videos are really informative and easily understandable. 😇🙏🏻😇

[@crackconcepts]

I’m so glad my videos helped you. Congratulations on the new job!

[@danushhu1317]

Shata 😂 dengda sariyagi

[@ranaprathap8292]

Bro any requirements for SQL pls reply me

[@kushalgupta8353]

Other Solution: select salary from (select salary from employee order by desc limit 3) order by salary asc limit 1;

[@Jagadish12345]

it’s not a different solution, it’s just different database’s query like top works for mssql where as limit works for mysql and postgresql

[@av98]

@@Jagadish12345 you are right For Oracle it's FETCH

[@thinkingmad1685]

@@Jagadish12345 similar way can you tell for sql plus

[@paramvirsohi5922]

The bestest and the easieST place ON ENTIRE INTERNET TO FIND ANSWER. TYSM. HELPED ME IN INTERVIEW

[@pratikkulkarni4929]

Absolutely super today this is asked in interview for me and today i got this answer

[@saiteja5246]

We can use select * from(select *,rank() over(order by sal desc) rno from emp)src where rno = Nth value

[@GANESH-zi2xb]

This query is wrong

[@himanshugupta4807]

In case of duplicate your query will not give expected result. We can use dense function instead rank function because rank give pit holes while generating rank on duplicates.

[@akulakalyan1242]

Select * from (select *, denserank() over (partition by empid order by sal desc)a,sal) src where src.a= Nth value;

[@himanshugupta4807]

@@akulakalyan1242 Hey Akula, query seems to be correct just one correction if you do partition over emp id then each salary will become unique and all records will have dense rank =1 so no need to use partition by clause.

[@akulakalyan1242]

@@himanshugupta4807 Then we can use dept id partition right

[@vireshmohan7554]

Hey, just wanted to let u knw that u r amazing.....The explanation Is just awesome...Thnx for such vids...Much Appreciated!!

[@dhivyakamalakannan]

Love this !! And I wish there was a love button in KZbin for subscribers

[@anissiddiqui2649]

Congratulations for 9 thousand subscribers.

[@raveendrakumar4499]

Excellent explanation, This is really helpful to understand the concept of this. Thank you so much for the video and keep posting interview based videos like this

[@lucky4y4]

Hi, I had watched your videos on SQL and it worked like a miracle for me. My logics gt better and better and finally I got the job offer that too on 31st of December 2020. I could never thank you. Thank you for your help. Keep posting keep helping keep growing. Thank you.

[@crackconcepts]

Congratulations 👏

[@zubairahmed-om4vd]

Very informative may Allah bless u

[@veenijeyasri2516]

Excellent mam, keep it up, expect more video from your page like this

[@mallikarjunhagargi7830]

You earned one subscriber dear thank you for the good content,,keep doing we appreciate

[@mcaddit6802]

Why top 1 keyword doesn't support in most of the SQL compiler?

[@ramanjaneyulum5739]

Select max(sal) from emp where salary not in(select distinct top 2 salary from emp order by salary describe)

[@rajeshg3570]

This is very good explanation.. really like it.

[@its_me7363]

very informative video...can you please make videos on more advanced SQL queries like partition, window function and pivoting table etc. ?

[@equiwave80]

I have just just started to learn SQL. These videos will be very useful for for the me!!! BTW I have subscribed to your channel!!!

[@ritika29]

Instead of TOP 1 in the outer query can we use min() function?

[@pratikkatke3570]

Yes u can

[@gopikrishna8521]

Yes u can add min

[@parthchoudhary4165]

Its really helpful .I understand in once ..tyku so much

[@guruquebec]

Nice and thanks. You should try to teach to pass exams like 70-761 topic wise. since you have good explaining skills.

[@anissiddiqui2649]

Congratulations to 10 thousands subscribers

[@crackconcepts]

Thank you😀

[@ramanmanocha4800]

Amazing content, eagerly waiting for more videos!!

[@RobbyTicknor]

You could use a window function kind of like this select * from ( select *,ROW_NUMBER() OVER(ORDER BY sal ASC) AS Row# from emp ) SubQueryWithRowNum where Row# = 3

[@ganeshkumaars859]

A DIRECT TOP 1 SELECTION MUST DO HOPE SO, AS WE ARE USING DESC AND AGIN MAKING IT ASC ,(the inner query must give result in asc by default)

[@RupinderKaur-oq4kl]

Thanks alott 💖very easy to understand ☺️

[@vishakhaborse7242]

Your teaching is really amazing...thank you

[@lavanyasakhamuri9693]

Very Nice explanation Mam.

[@anirudhsodhani9397]

what if we have a table that has many duplicate salary values? I think the better way to write the query is to fetch distinct N salary from the tables first ordered in descending order, and then finding out the min out of the N distinct salary/.

[@anissiddiqui2649]

Congratulations for 11 thousands subscribers

[@crackconcepts]

Thank youu😊

[@devanaidu9406]

Nice answer.. thank you for sharing your knowledge.❤️

[@vasanthavasu8696]

We can use min(sal) in outer query instead of top 1. Am I wright?

[@rushikeshkhandagale6472]

hi thanks for your SQL query lectures they are very helpful.

[@ranaprathap8292]

Bro any requirements for SQL server pls reply me

[@AbhishekSharma-hy4nl]

Medium level leetcode question ... I just tried to solve it but couldn't. Thnx alot...

[@sivaakumalla8687]

Nice explanation.👍 if it possible explain 'With '

[@aravindg4801]

Hi, From which version onwards TOP clause will work?? In oracle

[@talhamuneer2599]

Please Make Video Every Week!

[@Faisal1504]

Hi After the subquery I had to write as Temp - like give the subquery an alias then it worked. Is that correct way to do this qry? otherwise it says incorrect syntax near keyword order

[@surendraavvaru8536]

Select * from (select ename,esal,dense_rank()over(order by sal desc)rank from Emp) where rank=#

[@geniuschess7916]

perfect explanation ! very well understood :)

[@shushmamalhotra6538]

Mam plz provide a plat form where we can share some question in which we have problem related to SQL query

[@akshitdhadwal]

You explain things really well

[@kumarraju9784]

Select from emp1 rownum

[@beingrishi6450]

Can it be resolved by using row_number OVER clause ORDER BY SALARY DESC ???

[@Naresh_9182]

I think this query is not supported by Oracle SQL developer 19thC version

[@gbharathi5556]

Can u say what is the best approach to find n th highest salary....and explain it...

[@unknowninformation3529]

What about the nth minimum topic? If there is max it should have minimum also right

[@kevinj6279]

We can use aggregate funcs like min(salary) and max(salary) right?

[@Prisivi]

Yes

[@kunaldarwesh2481]

I like your explanation keep it up.

[@arunachaleshwartiruvannama3188]

Useful one Thanks for this

[@parshu3238]

Very well explain thank you very very much

[@hajaaa100]

For Nth highest salary in outer query do we need order by salary Asc.??? I feel not required but for 3rd highest it required. Correct me if I am wrong.

[@vishalsaxena5081]

i have a query pls hlp me to solve this column_1 A A1,B2,C5 how can we insert in column like this and after that i want change where A1 is given i need only A pls help

[@daminipatil8183]

Thank u so much Nice explanation

[@daminipatil8183]

Best teaching.. Thank u

[@jatinpant6992]

Mam, I am using MySQL and i am not able to use TOP Query, Please tell me what to do?

[@jaisingh1641]

Why internal query order is descending , why can we keep it ASC , then then later we would not need to order again...

[@siddharthkakade7779]

Select n salary and why do you select top 3 changes the question directly

[@AsimSamDesiVines]

Ma'am Will it work for repetitive salary data???

[@suhas3785]

Thx for create video like this.....

[@mercifulmedia9393]

Tittle mistake: "Nth" Don't mind.

[@Vivek-gx6zd]

select salary(select salary from employees order by salary dec)where rownum

[@priyaingle832]

Wow so easy explaination,

[@shubhamingale4625]

Is there any other trick in which we don't need use inner outer query

[@vyshnavib8023]

writing inner query only sufficient no mam..why can we write outerquery also..explan mam

[@mazingTechnical]

select * from emp order by salary desc limit 2,1;

[@xyz-vv5tg]

SELECT DISTINCT Sal FROM emps ORDER BY Sal LIMIT n; Here n =3 This might work as well.

[@jayantmishra4270]

3rd height salary:- Can i write like this?? :- Select salary from employees order by salary desc limit 2,1

[@MaheshKumar-dj2nj]

i use that query subquery worked but above condition not working alert was invalid column name

[@chinmayk2657]

Maam - need help - How to find people in a database who live in the same cities?

[@sohrabkhan9823]

AOA, Urdu may banao na videos mam, or computr pr practical bhi sikhaiye plz

[@Swatigowda0222]

Can you make a video on abstraction in Java please? Because the way you’re teaching is understandable easily.

[@rgvlogs1232]

On this question , Can I use Select max (salary) from sal

[@jagadishteluguvlogs]

Hlo mam I have one dought ... Given requirement is fetch the highest salary in a table so your are said top 1 salary in a table but but inner query results give 6000,5000,3000 like that but in this salary top 1 salary is 6000 but your said 3000 why mam..plz clearly say that mam IAM full confused..

[@CS-Gowtham-R]

Hi mam, I need one query. Write a query to select the nth row from the table. In MySQL. Please tell me mam❣️🙏

[@raktimghosh7513]

Select * from Table name where ROWNUM=N;

[@CS-Gowtham-R]

@@raktimghosh7513 No no it gives 0 to n rows. But I need only the specific nth row.

[@sngparminder]

I tried but the top query but it is not working, I use toad & my sql

[@v_naykp4168]

Select * from employees Order by Salary Desc Limit 3 ;

[@sanjayisure4580]

I am day by day going depressed bcz of not able to crack interview , they given scinario which is not solve by me , so please help me

[@nikhilkadadevar5289]

Take 3 tables and solve query including all 3 tables mam

[@channushahapur3019]

Can we use order by in the inner query??

[@rushikeshkhandagale6472]

what does mean Data integrity and how we check data integrity ?

[@bhavinjoshi6780]

Hi. How about using limit and offset?

[@tanvirkaur5013]

Limit jus doesnt work in oracle.

[@bhavinjoshi6780]

@@tanvirkaur5013 But here we are talking about MySql. Right? Or no?

[@vedantpatil3752]

Thank you soo much god bless you

[@souravnandy8385]

Is there any other way to write this query?

[@rayavenugopal8847]

Mam i need full course training on plsql. Can u?

[@medhakhairnar6142]

Thanks ditto same question came in the interview

[@mcaddit6802]

SELECT Salary FROM (SELECT distinct Salary FROM worker ORDER BY salary DESC LIMIT 2) as sal ORDER BY salary LIMIT 1;

[@shutzzzzzz]

What if we have duplicate data

[@AsadAhmad-hd3gn]

pahle distinct use karo

[@junirox1]

Hello, really liked your videos. Thank you so much. I have more specific questions regarding SQL and I need help. Can I please email you. KIND REGARDS, JUN FROM UK

[@mohammedhanifakolewale17]

If department Id and salary in the same table how we can find hights salary with respect to department

[@mohammedhanifakolewale17]

I want only display highest salary with respect to department

[@isankalp27]

Hi plz teach us SQL queries on the level of Class 10

[@apamwamba]

Given say: Person Salary tom 10 mary 20 hillary 30 joe 10 apam 200 cecilia 30 zuk 30 hope 80 euphrasia 100 Maureen 150 SELECT [Person] ,[Salary] FROM ( SELECT [Person] ,[Salary] ,DENSE_RANK() OVER (ORDER BY Salary) as ranking FROM [dbo].[sal_01] ) AS x WHERE x.ranking=3 RESULT: Person Salary hillary 30 cecilia 30 zuk 30

[@trilok13579]

I want to display Name with Salary how can i do?

[@pratiknarkhede1287]

Is there a function called Top?

[@sngparminder]

I wonder not, I tried this query sql toad but didn't worked