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



IBM's flagship relational database management system

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

Postby pradeepgowda » Tue Mar 23, 2010 11:38 am

Hi,

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

Thanks
pradeepgowda
 
Posts: 40
Joined: Mon Jan 04, 2010 4:17 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby Chaitnya » Tue Mar 23, 2010 5:42 pm

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 :?:
Chaitnya
 
Posts: 18
Joined: Wed Mar 26, 2008 1:04 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby pradeepgowda » Wed Mar 24, 2010 12:03 pm

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
pradeepgowda
 
Posts: 40
Joined: Mon Jan 04, 2010 4:17 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby Chaitnya » Wed Mar 24, 2010 2:55 pm

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
Chaitnya
 
Posts: 18
Joined: Wed Mar 26, 2008 1:04 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby GuyC » Tue Mar 30, 2010 7:51 pm

"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)))
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

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

Postby Chaitnya » Tue Apr 06, 2010 4:31 pm

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
Chaitnya
 
Posts: 18
Joined: Wed Mar 26, 2008 1:04 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby pradeepgowda » Wed Apr 07, 2010 11:43 am

from the guyc answer we can retrieve only 3rd maximum salary. but i want first three maximum salaries
pradeepgowda
 
Posts: 40
Joined: Mon Jan 04, 2010 4:17 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby Chaitnya » Wed Apr 07, 2010 3:28 pm

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.
Chaitnya
 
Posts: 18
Joined: Wed Mar 26, 2008 1:04 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby pradeepgowda » Wed Apr 07, 2010 4:33 pm

2) will work chaitnya.
do you know how to implement the same using correlated subquerry
pradeepgowda
 
Posts: 40
Joined: Mon Jan 04, 2010 4:17 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby GuyC » Thu Apr 08, 2010 4:03 pm

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

2) "fetch first n rows" is a functionality that IBM didn't implement until DB2 V8.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post