Jayakumar M

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

Anonymous

• 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

Elango

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

pktelango

 Profile Answers by pktelango 

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

Nth MAx

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

Rahman

• Jul 17th, 2006

 

Pls Explain the query

rashmi Shan

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

sheetalkamthe

• Apr 4th, 2007

 

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

Faizal

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

Amol Pawar

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

Lokesh.V

• 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:

sridharrganesan

 Profile Answers by sridharrganesan 

• Oct 22nd, 2007

 

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;

ghoshkunal123

 Profile Answers by ghoshkunal123 

• Jan 29th, 2008

 

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

mohd sajjad

 Profile Answers by mohd sajjad 

• Jun 25th, 2008

 

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;

prateekin

 Profile Answers by prateekin 

• Oct 3rd, 2008

 

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

s2r2v2n2n

 Profile Answers by s2r2v2n2n 

• Nov 1st, 2009

 

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;

satheyaraaj

 Profile Answers by satheyaraaj 

• Jul 9th, 2011

 

SELECT TOP 1 Grosspay

FROM (

SELECT DISTINCT TOP 4 Grosspay

FROM SalaryMaster

ORDER BY Grosspay DESC) a

ORDER BY Grosspay

krishna

• Jul 17th, 2011

 

DB2:

Code

1.  

YOGESH

• Jun 6th, 2017

 

SELECT * FROM EMP A WHERE &N>= (SELECT COUNT(DISTINCT SALARY)FROM EMP B WHERE B.SALARY>=A.SALARY);

Ranaj KumarParida

• Jul 28th, 2017

 

SELECT MAX(SAL),LEVEL FROM EMP
WHERE LEVEL &LEVEL
CONNECT BY PRIOR SAL > SAL
GROUP BY LEVEL
ORDER BY LEVEL;

BRAHMAM

• Dec 6th, 2017

 

SELECT * FROM EMP E1 WHEERE N-I=(SELECT COUNT(DISTINCT SAL) FROM EMP E2 WHERE E2.SAL>E1.SAL