**Jayakumar M**
Answered On : Nov 8th, 2005

select * from (select rownum rn,sal from (select distinct sal From emp order by sal desc)) where rn between 4 and 5;

Login to rate this answer.

**Anonymous**
Answered On : Dec 25th, 2005

If the DB does not support ranking/rownumbering (like SQL 2000), then that's a bit different. This is just a simple way to do this, probably not the most optimal (please test):select top5.value from(select top 5 value from table t order by t.value asc) as top5left outer join(select top 3 value from table t order by t.value asc) as top3on top5.value = top3.valuewhere top3.value is null

Login to rate this answer.

**Elango**
Answered On : Jan 19th, 2006

4th MAX

select max(id) from EMP A where 4=( select count(id) From EMP B where B.ID>=A.ID)

5th MAX

select max(id) from EMP A where 5=( select count(id) From EMP B where B.ID>=A.ID)

Login to rate this answer.

4th MAx

select max(id) from EMP A where **4**=( select count(id) From EMP B where B.ID>=A.ID)

5th MAx

select max(id) from EMP A where **5**=( select count(id) From EMP B where B.ID>=A.ID)

Nth MAx

select max(id) from EMP A where N=( select count(id) From EMP B where B.ID>=A.ID)

Login to rate this answer.

**Rahman**
Answered On : Jul 17th, 2006

Pls Explain the query

Login to rate this answer.

**rashmi Shan**
Answered On : Jul 31st, 2006

To get 4th maximum value

SELECT

MAx(salary) FROM EMP WHERE SALARY NOT IN (SELECT top 3 quantity FROM EMP ORDER BY salary DESC)

To get nth max value

MAx(salary) FROM EMP WHERE SALARY NOT IN (SELECT top n-1 quantity FROM EMP ORDER BY salary DESC)

Login to rate this answer.

**sheetalkamthe**
Answered On : Apr 4th, 2007

select top 1 salary from (select top 4 salary from tblEmployee order by asc)

Login to rate this answer.

**Faizal**
Answered On : Jul 6th, 2007

/*

Please try to enter the value of 'N' and you can retrieve that row

*/

select * from emp where sal=

(

select min(sal) as Salary from

(select sal from (select distinct sal from emp) order by sal desc) where rownum <= &n

)

Login to rate this answer.

**Amol Pawar**
Answered On : Jul 16th, 2007

**Select 5th max salary**

SELECT * FROM emp e1 WHERE (5 = (SELECT COUNT(DISTINCT (e2.sal))

FROM emp e2 WHERE e2.sal >= e1.sal))

Login to rate this answer.

**Lokesh.V**
Answered On : Jul 20th, 2007

Hi

Pls find the generalised Sql statement put n value that it!

select distinct (a.sal) from emp a where &n=(select count (distinct(b.sal)) from emp b where a.sal<=b.sal);

it gives enter the value for n:

Login to rate this answer.

select * from table_name where (select rownum rn,sal from (select distinct sal From emp order by sal desc)) where rn between 4 and 5;

Login to rate this answer.

Use Rank() or Dense_Rank() to make life easier.

Login to rate this answer.

select sal from(select sal from emp order by sal desc) where rownum<=4 minus select sal from(select sal from emp order by sal desc) where rownum<=3;

Login to rate this answer.

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

Use the below query, this works fine to find the 4th and 5th maximum salary of an emp table. You can change the numbers in where condition to get the nth maximum.

SELECT a.*, b.rn

FROM emp a, (SELECT emp_id, rownum rn FROM emp ORDER BY salary DESC) b

WHERE a.emp_id = b.emp_id

AND rn BETWEEN 4 AND 5;

Login to rate this answer.

**SELECT TOP 1 Grosspay**

**FROM (**

S*ELECT DISTINCT TOP 4 Grosspay*

*FROM SalaryMaster*

*ORDER BY Grosspay DESC) a*

**ORDER BY Grosspay**

Login to rate this answer.

**krishna**
Answered On : Jul 17th, 2011

DB2:

Code

SELECT DISTINCT sal FROM emp A WHERE n= (SELECT count(DISTINCT sal) FROM emp B

WHERE A.sal <= B.sal)

Login to rate this answer.