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

Questions by vidhyalakshmi

Showing Answers 1 - 18 of 18 Answers

faizal khan

  • 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

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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;

  Was this answer useful?  Yes

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

 

  Was this answer useful?  Yes

Rohan Deshpande

  • Oct 13th, 2006
 

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

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

Das

  • Nov 2nd, 2006
 

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

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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.

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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;

  Was this answer useful?  Yes

SAPNA

  • Dec 20th, 2006
 

HI

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

  Was this answer useful?  Yes

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)

  Was this answer useful?  Yes

ismailbegum

  • Dec 21st, 2009
 

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

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

kitty

  • Sep 8th, 2014
 

can u plz explain me in detail about the answer you have provided with correlated sub query.
Thnks in advance !

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Answer Question

Click here to Login / Register your free account


 
Send   Reset

 

Related Answered Questions

 

Related Open Questions