GeekInterview.com
Series: Subject: Topic:
Question: 247 of 429

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

Asked by: vidhyalakshmi | Member Since Jun-2006 | Asked on: Sep 16th, 2006

View all questions by vidhyalakshmi

Showing Answers 1 - 18 of 18 Answers
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 by 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.unitpricefrom products p1where(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.unitpricefrom products p1where (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

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

Yes  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 salaryMETHOD 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. ThanksShilpa

  
Login to rate this answer.
prabuS

Answered On : Nov 14th, 2006

hello,try this query1.this is for nth highest noselect 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 noselect 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

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

Answered On : Dec 21st, 2009

View all answers by ismailbegum

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 = &nSQL> /Enter value for n: 4old   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 = &nnew   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 = 4LAST_NAME                     SALARY------------------------- ----------Hartstein                      13000//lyYou 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 = &nSQL> /Enter value for n: 4old   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 = &nnew   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 = 4LAST_NAME                     SALARY------------------------- ----------Rajs                            3500

  
Login to rate this answer.
kitty

Answered On : Sep 8th, 2014

Can u plz explain me in detail about the answer you have provided with correlated sub query.

Thnks in advance !

  
Login to rate this answer.

Give your answer:

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

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.