16. Nth highest salary (Top 50 SQL Interview Questions) | GeeksforGeeks

Find Complete Code at GeeksforGeeks Article:

This video is contributed by

Please Like, Comment and Share the Video among your friends.

Install our Android App:

If you wish, translate into local language and help us reach millions of other geeks:

Follow us on Facebook:

And Twitter:

Also, Subscribe if you haven't already! :)

[arun_vignesh-]

select min(sal) from(select distinct sal from emp order by sal desc)
where rownum<=3;
without minus we can use it in this way

[rohan]

*Simple way to find Nth highest salary:*

select min(salary) from
(select distinct salary from employee order by salary desc)
where rownum <= N;

[aakashjadhav]

with salary_Details as (
select emp_name, emp_sal, dense_rank () over ( order by emp_sal desc) as rank from employee)
select * from salary_Details
where rank = 3;

[lakshmiprasanna]

We can use this "Select * from emp( Select rownum r, distinct(sal) from emp order by desc) where r = 3;"

[maniganesh]

SELECT DISTINCT Salary as EighthDistinctHighestSalary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 7;
by changing the offer we can obtain the nth number

[DANISHKALEEM-rd]

The easiest way is using offset and fetch, let me give u an example, if we have to fetch 4th higest salery, we will use query like this : " select distinct salery from Employee order by salery desc offset 3 row fetch next 1 row only ". offset is for skip and fetch is for limit, i hope u understand . if u want me to explain more reply to the comment. thank u

[sezgin]

this is a great content, have been looking such a content for days

[animeprofiles]

Man u gave such a general startegy 👍 so awesome
For mysql users
U can use TOP clause in place of rownum

[Veeravalli]

select distinct sal emp where rownum = n order by sal desc;

[shubhambhardwaj]

Just order by desc and use LIMIT 1 and OFFSET N?

[RahulMishra-pldq]

Just replace n with the value you want

WITH CTE AS
(SELECT
*,
nth_value(sal, n) OVER(ORDER BY sal DESC) as nvalue
FROM emp)


SELECT * (OR DISTINCT * to remove the duplicates)
FROM CTE
WHERE nvalue IS NOT null;

[rexsmithcapgemini]

rownum is not available in other db. these sql are Oracle specific

[raghavanrags]

Why can we directly use the query without like an inline kind of query..i mean why cant we use select distinct sal from emp order by sal desc where rownum<=3 minus select distinct sal from emp order by sal desc where rownum<=2;

[Iwitrag]

I would wrap it into another sub-query and assign rownum alias... then in outer query you could just check for equals

[MegaEbenezar]

We can use the below query
select max(sal) from(select distinct Sal from Emp order by sal desc) where rownum<= 3

[vikubaba]

INSTEAD OF DOING ALL THOSE THING WE CAN TRY IT IN SHORT
SELECT TOP 1 COLUMN_NAME
FROM
(SELECT TOP 3 COLUMN_NAME
FROM TABLE_NAME
ORDER BY COLUMN_NAME DESC) AS XX

ORDER BY COLUMN_NAME ASC ;

[ankitshrivastava]

getting error minus identifier is not valid at this position in the query,

SELECT * FROM(SELECT DISTINCT MARKS FROM tbl_studentData order by MARKS desc )
where rownum <=3

minus

SELECT * FROM(SELECT DISTINCT MARKS FROM tbl_studentData order by MARKS desc )
where rownum <=2

[dineshpandey]

Greate sir, it can be done by using = operator instead of minus operator.

[vishnuvamshi]

facing problem please provide the solution the screen is showing like this

Incorrect syntax near the keyword 'where'.

select distinct salary from empdetail order by salary desc
where rownum<=3;
minus
select distinct salary from empdetail order by salary desc
where rownum<=2;

[ridwanmugdha]

SELECT * FROM Employee WHERE sal =
(
SELECT MIN(sal) FROM Employee
WHERE sal IN (
SELECT DISTINCT TOP N
sal FROM Employee
ORDER BY sal DESC
)
)