How to find third highest salary without using Top #sql #sqlqueries #sqlinterview #coding #shorts

How to find third highest salary without using Top #sql #sqlqueries #sqlinterview #coding #shorts #youtubeshorts #programming

[caveatashish]

with cte as
(
select *, dense_rank() over (order by salary desc) as cnt
from EmployeeSalaries
)
select * from cte where cnt =3

[gaziabbas]

Select * from employee order by salary desc offset 2 rows fetch next 1 row only

[asthitoyp]

Owhh that's interesting, but here's my take :

SELECT * FROM (SELECT * FROM Employee ORDER BY salary DESC LIMIT 3) a
ORDER BY salary ASC LIMIT 1

Thought Proccess : If we wanted to find the n number, we could just make sub query first to position the row we wanted to find, in the bottom / top position using LIMIT, and using that sub query we could find the n row in the top / bottom position using LIMIT too

So what i using here is pretty much just ORDER BY row_name ASC / DESC and LIMIT n

Let me know your though, and if it's right or wrong!

It's interesting little challenge, thanks for the learning experience!

[chandrasekar]

Select * from(select rownum r, Ename, sal from emp order by sal desc)
Where r=3;

[sohelalamrana]

In the first example query, the second order by is not required as we are selecting only 1.

[vinaykumarboddu]

How about use cte instead of sub query??
I mean sub query will effect the performance...

[gleamofcolor]

The second order by is not needed as the sub query picks the top 3 records in asc order so only top 1 is sufficient