fetch

How to find second highest salary in SQL



Sharing buttons:

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