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

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

ramesh

Oct 3rd, 2006

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;

Mamata Kalbande

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

Rohan Deshpande

Oct 13th, 2006

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

ALBERT ASHISH

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

GC Shekar

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

Das

Nov 2nd, 2006

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

Syed Jawahar

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

parvathi

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.

Shilpa Boranaa

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

prabuS

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);

Manish Agarwal

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;

SAPNA

Dec 20th, 2006

HI

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

bhumip

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)

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

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

## How to find Nth largest or Nth smallest data from oracle table, for ex..5th highest salary from employees

vidhyalakshmiProfile Answers by vidhyalakshmi Questions by vidhyalakshmi

Questions by vidhyalakshmi

## Answer Question

Click here to Login / Register your free account

## Related Answered Questions

## Related Open Questions