**faizal khan**
Answered On : Sep 21st, 2006

This is one of the solutions to it. there are many more.select * from ( select last_name,salary,dense_rank() over (order by salary desc) rank from EMPLOYEES) where rank=10

Login to rate this answer.

1 st methodselect empno from (select empno,rownum rn from emp order by empno desc) it where it.rn=22 nd methodselect max(empno) from emp where level=2 connect by prior empno>empno group by level;http://360.yahoo.com/suresh.kandukuru

Login to rate this answer.

**ramesh**
Answered On : Oct 3rd, 2006

try this one..hope it wil be easy..

**select** rownum,sal **from** (select distinct sal from emp order by sal) **group b**y rownum,sal** having** rownum=5;

Login to rate this answer.

**Mamata Kalbande**
Answered On : Oct 4th, 2006

To get the nth largest unitprice from products table:

SELECT distinct p1.unitprice

from products p1

where

(select count(*)+1 from products p2 where

p2.unitprice > p1.unitprice) = {n}

order by p1.unitprice desc

To get the nth smallest unitprice from products table:

SELECT distinct p1.unitprice

from products p1

where

(select count(*)+1 from products p2 where

p2.unitprice < p1.unitprice) = {n}

order by p1.unitprice asc

Login to rate this answer.

**Rohan Deshpande**
Answered On : Oct 13th, 2006

query select * from(select ename,sal from emp order by sal desc) where rownum<=5;

Login to rate this answer.

**ALBERT ASHISH**
Answered On : Oct 18th, 2006

To find the nth higest salary:

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

To find the nth smallest salary:

select max(sal) from (select distinct sal from emp order by sal) where rownum<=&n;

Dont get confused... its min(sal) for highest and max(sal) for lowest. Becos:

Ex: Top 3 salaries - 5000,3000,2975 for 3rd higest min(sal) i.e 2975...

1 User has rated as useful.

Login to rate this answer.

**GC Shekar**
Answered On : Nov 1st, 2006

Assume you have Table T1 with Columns C1 containing the Name and Salary in C2.

Below SQL would give the required result:

**SELECT * **

FROM (SELECT c2, ROWNUM rn

FROM (SELECT DISTINCT c2

FROM t1 ORDER BY c2)

)

WHERE rn = <nth smallest data>

Good Luck!

Ganesh

Login to rate this answer.

**Das**
Answered On : Nov 2nd, 2006

SOrry this does not give the desired result. It returns the n number of records in desc order

Login to rate this answer.

**Syed Jawahar**
Answered On : Nov 4th, 2006

hi please find the exact solution for the nth highest salary.

METHOD 1:

select empname,salary from emp where sal= ( select min(sal) from (select distinct sal from emp order by sal desc) where rownum<=n)

n---> nth salary

METHOD 2:

SELECT ename,sal from emp a where (n-1) =(select count(*) from (select distinct(sal) from emp) b where b.sal>a.sal)

for eg to calculate 5th highest salary. substitute 4 in where clause(ie 5-1)

Explanation of the query:

For every row in emp. Count the number of salaries greater than that row. when you find a row with only 3 greater salaries you must have the 4th highest right there.

NOTE: please dont write statements like 'where rownum=n'. It wont work at all. because rownum will be generated at runtime. so rownum<=5 is correct and rownum=5 is wrong(means it wont give any error but will not return any rows) You can write only rownum=1 but not 2,3 etc..

Login to rate this answer.

**parvathi**
Answered On : Nov 8th, 2006

Hi,

Using Correlated Query it is possible to find 5th or nth hight salary.The Correlated Query is,

"select a.sal from emp a where 5 =(select count(distinct(b.sal)) from emp b where a.sal<=b.sal)."

in place of 5 we can use nth salaray also.

Thanks,

Parvathi.

Login to rate this answer.

**Shilpa Boranaa**
Answered On : Nov 14th, 2006

Parvathi,

I ahve seen this query many times, but I just forgot teh logic behind this. Could you please explain that.

Thanks

Shilpa

Login to rate this answer.

**prabuS**
Answered On : Nov 14th, 2006

hello,

try this query

1.this is for nth highest no

select distinct(a.sal) from emp a where &n=(select count(distinct(b.sal)) from emp b where a.sal<=b.sal);

2.this is for nth lowest no

select distinct(a.sal) from emp a where &n=(select count(distinct(b.sal)) from emp b where a.sal>=b.sal);

Login to rate this answer.

**Manish Agarwal**
Answered On : Dec 18th, 2006

Analytic functions can be used:

Nth highest salary :-

Select empid, sal from (Select empid, sal, dense_rank() over(order by sal desc) rk from emp) where rk = N;

Nth smallest salary :-

Select empid, sal from (Select empid, sal, dense_rank() over(order by sal asc) rk from emp) where rk = N;

Login to rate this answer.

**SAPNA**
Answered On : Dec 20th, 2006

HI

SELECT TOP1 SAL ( SELECT DISTINCT TOP FIVE SAL FROM EMP ORDER BY DESC)

Login to rate this answer.

**bhumip**
Answered On : Jan 1st, 2007

select top 1 * from (select top 5 * from stud ) temp order by id desc (5 th heighst)select top 1 * from (select top 6 * from stud ) temp order by id desc (6 th heighst).....select top 1 * from (select top n * from stud ) temp order by id desc (n th heighst)

Login to rate this answer.

Nth largest :

select * from emp e where &n=(select count(distinct(salary)) from emp where e.salary <=salary);

Nth smallest : select * from emp e where &n=(select count(distinct(salary)) from emp where e.salary >= salary);

Login to rate this answer.

4th Highest Salary

1* select a.last_name,a.salary from (select b.*,rownum rn from (select last_name,salary from employees order by salary desc)b)a where rn = &n

SQL> /

Enter value for n: 4

old 1: select a.last_name,a.salary from (select b.*,rownum rn from (select last_name,salary from employees order by salary desc)b)a where rn = &n

new 1: select a.last_name,a.salary from (select b.*,rownum rn from (select last_name,salary from employees order by salary desc)b)a where rn = 4

LAST_NAME SALARY

------------------------- ----------

Hartstein 13000

//ly

You can say 4 Lowest Salary

1* select a.last_name,a.salary from (select b.*,rownum rn from (select last_name,salary from employees order by salary )b)a where rn = &n

SQL> /

Enter value for n: 4

old 1: select a.last_name,a.salary from (select b.*,rownum rn from (select last_name,salary from employees order by salary )b)a where rn = &n

new 1: select a.last_name,a.salary from (select b.*,rownum rn from (select last_name,salary from employees order by salary )b)a where rn = 4

LAST_NAME SALARY

------------------------- ----------

Rajs 3500

Login to rate this answer.