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

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

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

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

 

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

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

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

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.

Parvathi,

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

Thanks

Shilpa

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

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;

HI

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

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)

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

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