IBM Mainframe Forum

Hi,

Please give me querry for retrieving three maximum salary in a single sql querry from a table for db2 version 6

Thanks

Please give me querry for retrieving three maximum salary in a single sql querry from a table for db2 version 6

Did you mean first three max from table or third max from table? Please clarify.

Chaitnya

yes, first three maximum salaries in a table

for example:
name colomn
aaa 1000
bbb 6000
ccc 3000
ddd 8000
eee 2000


the result must be
name column
ddd 8000
bbb 6000

Thanks
ccc 3000

Hi,

Please customize below sample query as per your table need and let us know after your trial(Not sure about DB2 version 6 is good to run).

SELECT SALARY FROM EMP
ORDER BY SALARY DESC
FETCH FIRST 3 ROWS ONLY;


Chaitnya

"fetch first n rows only" doesn't work until V8

Hi GuyC,

I think top most query in your reply has "=" operator which will fetch the 3rd max, where as pradeepgowda needs first three maximum. so I think operator should be ">=". And thanks for confirming

"fetch first n rows only" doesn't work until V8

Please clarify:!:

Thanks,
Chaitnya

from the guyc answer we can retrieve only 3rd maximum salary. but i want first three maximum salaries

Hi pradeepgowda,

First of all please let us know which are the queries have you tried in DB2 V6 to find out first three max.???

1)
SELECT SALARY FROM EMP
ORDER BY SALARY DESC
FETCH FIRST 3 ROWS ONLY;

2)
select sal from emp where
sal >= (select max(sal) from emp
where sal < (select max(sal) from emp
where sal < (select max(sal) from emp)))

If 1st would not work on DB2 V6 then 2nd would definitely solve your requirement.

2) will work chaitnya.
do you know how to implement the same using correlated subquerry

@Chaitya:
1) yes, it should have been >=

2) "fetch first n rows" is a functionality that IBM didn't implement until DB2 V8.