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

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.

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.