How to find second highest salary in SQL

today we're going to discuss about already important interview questions and that is to find the second highest salary using my using SQL so basically there are three ways which we can use that one is max second is limit throw to stop let's go one by one the first one is max this is the query for it select max salary from employee where salary is less and select max salary from Roy one thing that we need to understand is that the query in the braces is known as an inner query and that is always executed before the outer query so in our example let's take this example of this table so the inner query says select max salary from employee so the at the back end what is gonna happen is the maximum salary is going to be selected at a 7000 and this this inner query is going to return 7000 okay so you're basically there are seven rows now the outer query is select maximum salary from employee where that salary is less than 7000 so basically the maximum salary which is less than 7000 will be printed and that would be 6000 so this is a query using max I hope it is clear and the second query the second way is limit using limit again select salary from there's an inner query as impe order by salary limit one salary and the inner query is the first our inner query is going to be executed so our inner query is select salary from employee order by salary this test limit to this means descending order and if you do not write anything like this by default it is ascending order so our inner query is going to select salary from employee order by so order by what does order by exactly do is it will sort it in one particular order so it is going to order by order by what Syrus is gonna order by the salary in the descending order and what does it is sending means this any means big or too small so it's going to be seven thousand six thousand five thousand two thousand limit to means only the to only the top two thirty seven thousand six thousand will be returned by this now our outer query what is auto query select salary from employee and the inner query is only save it on six thousand as employee order by salary now order by salary it means ascending order so now seven thousand six thousand in the ascending order would will become 6,000 7,000 because i sending always smaller to 6000 7000 and limit one means only one will be returned so in the end 6000 will be moved on and that is our answer I hope this clear and a third query stop so select top one salary from the inner query as in one order by salary aside remember in limit and in top order by is used basically we are going to order it in ascending or descending order and then we are going to take the top one or limit one of something like that also so remember order by is going to be in that okay so now the inner query says select talk to salary from employee order by Channel so first of all the table is going to be ordered it's going to be sorted in descending order so it's going to be seven thousand six thousand and since it is doing pop - so it's gonna take talk to seven thousand six thousand so this inner query is going to return seven thousand and six thousand now what the outer query see is select the top one but top but we are gonna order it in ascending order so it's gonna be six thousand seven thousand and the top one is going to be taken in that is six thousand so select top one salary from in a query order by side reassigned so so I hope it is clear thank you so much

Channel: Crack Concepts

Views: 141,093

Rating: 4.9131713 out of 5

Keywords:

Id: a3ngELoA9h0

Length: 4min 10sec (250 seconds)

Published: Tue Nov 06 2018