SQL Plus - Find out nth highest salary from emp table

GeekInterview.com > Interview Questions > Oracle > SQL Plus

Go To First | Previous Question | Next Question

SQL Plus | Question 39 of 132

Find out nth highest salary from emp table

Total Answers and Comments: 12

Last Update: September 12, 2008

Sponsored Links

Best Rated Answer

—

Submitted by: Ajay

select sal from emp e where n-1=(select count(distinct sal) from emp where sal>e.sal);

Above answer was rated as good by the following members:
harinath_btech, Sowmya Viswanath

Sorting Options

Page 1 of 2

« First

Last »

March 24, 2005 15:09:36

Ajay

RE: Find out nth highest salary from emp table

select sal from emp e where n-1 (select count(distinct sal) from emp where sal>e.sal);

Is this answer useful? Yes | No

1 0

Overall Rating: +1

March 26, 2005 14:09:25

AjayLebaka

RE: Find out nth highest salary from emp table

select sal from emp E
wherenulln-1 (select count( distinct sal) from emp where
sal>E.sal);

Is this answer useful? Yes | No

March 23, 2006 14:14:51

padma

RE: Find out nth highest salary from emp table

select max(sal) from (select distinct sal from emp where rownum< (n+1) order by sal)

where n is the nth position.

Is this answer useful? Yes | No

May 16, 2006 16:11:30

rajanipriya

Member Since: May 2006 Contribution: 19

RE: Find out nth highest salary from emp table

Mr Ajay

Sir i couldnot understand the query u wrote for nth highest salary.

please explain in detail!!!!

Is this answer useful? Yes | No

July 24, 2006 10:24:37

Shiv Mangal Rahi

RE: Find out nth highest salary from emp table

Try this one.I hope u could able to get it

SQL> select min(sal) from (select sal from emp order by sal desc)
where rownum<n+1;

Is this answer useful? Yes | No

1 0

Overall Rating: +1

December 07, 2006 09:43:51

rampratap409

Member Since: September 2006 Contribution: 111

RE: Find out nth highest salary from emp table

select * from emp where rownum< 9

minus

select * from emp where rownum <9

Is this answer useful? Yes | No

December 27, 2006 21:12:25

sudhaker

Member Since: December 2006 Contribution: 2

RE: Find out nth highest salary from emp table

select min(sal) from (select distinct(sal) from emp order by sal desc) where rownum< '&n';

Is this answer useful? Yes | No

June 01, 2007 09:04:48

Sangeeta

RE: Find out nth highest salary from emp table

this is the best solution to find out nth highest solution : select min(sal) from emp where sal in (select sal from (select sal from emp order by sal desc) where rownum < &n )

Is this answer useful? Yes | No

September 20, 2007 01:09:48

soorajsk_84

Member Since: June 2007 Contribution: 28

RE: Find out nth highest salary from emp table

SELECT ename sal FROM emp s

WHERE (n-1)

(SELECT count(*) FROM emp e

WHERE e.sal>s.sal)

This will return you the right result

Is this answer useful? Yes | No

June 21, 2008 01:27:53

#10

G.D.S.Prasad

Member Since: November 2006 Contribution: 191