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

select rownum sal from (select distinct sal from emp order by sal) group by rownum sal having rownum 5;

To get the nth largest unitprice from products table:

SELECT distinct p1.unitprice

from products p1

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

p2.unitprice > p1.unitprice) {n}

order by p1.unitprice desc

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...

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

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..

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.