Page 1 of 1

to retrieve 3 maximum salary in a single querry in db2 v6

PostPosted: Tue Mar 23, 2010 11:38 am
by pradeepgowda
Hi,

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

Thanks

Re: to retrieve 3 maximum salary in a single querry in db2 v6

PostPosted: Tue Mar 23, 2010 5:42 pm
by Chaitnya
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 :?:

Re: to retrieve 3 maximum salary in a single querry in db2 v6

PostPosted: Wed Mar 24, 2010 12:03 pm
by pradeepgowda
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

Re: to retrieve 3 maximum salary in a single querry in db2 v6

PostPosted: Wed Mar 24, 2010 2:55 pm
by Chaitnya
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 :D

Re: to retrieve 3 maximum salary in a single querry in db2 v6

PostPosted: Tue Mar 30, 2010 7:51 pm
by GuyC
"fetch first n rows only" doesn't work until V8

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

Re: to retrieve 3 maximum salary in a single querry in db2 v6

PostPosted: Tue Apr 06, 2010 4:31 pm
by Chaitnya
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

Re: to retrieve 3 maximum salary in a single querry in db2 v6

PostPosted: Wed Apr 07, 2010 11:43 am
by pradeepgowda
from the guyc answer we can retrieve only 3rd maximum salary. but i want first three maximum salaries

Re: to retrieve 3 maximum salary in a single querry in db2 v6

PostPosted: Wed Apr 07, 2010 3:28 pm
by Chaitnya
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.

Re: to retrieve 3 maximum salary in a single querry in db2 v6

PostPosted: Wed Apr 07, 2010 4:33 pm
by pradeepgowda
2) will work chaitnya.
do you know how to implement the same using correlated subquerry

Re: to retrieve 3 maximum salary in a single querry in db2 v6

PostPosted: Thu Apr 08, 2010 4:03 pm
by GuyC
@Chaitya:
1) yes, it should have been >=

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