| View previous topic :: View next topic |
| Author |
Message |
maverick05
Beginner
Joined: 15 May 2005
Posts: 72
Topics: 24
|
 Posted: Sun Apr 19, 2009 10:09 pm Post subject: Sql query for getting max value based on other column |
 |
|
Hi,
I have the following 3 rows in db2 table.Table has 3 columns X,Y,Z
| Code: |
X Y Z
A 1 2001-01-01
A 2 2001-01-01
A 3 2002-02-02
|
Now my req is that i need to fetch the row with max Y column value(max(Y)) if and only if date 2001-01-01 is present in any of the 3 rows(Note that column1 value is same for all the 3 rows)
How can i achieve the result using a simple query.if not possible with a single query,what is the best query can be written to get the desired output.
so my output should be the last row with max column Y value .i.e 3
Thanks for your help |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Sun Apr 19, 2009 10:29 pm Post subject: |
|
|
maverick05,
Try this untested sql
| Code: |
SELECT X
,MAX(Y)
FROM TABLE
WHERE X IN (SELECT X FROM TABLE WHERE Z = '2001-01-01')
GROUP BY X
;
|
Kolusu |
|
| Back to top |
|
|
maverick05
Beginner
Joined: 15 May 2005
Posts: 72
Topics: 24
|
| Posted: Mon Apr 20, 2009 7:22 am Post subject: |
|
|
| Hi Kolusu,i need to fetch the third row but not the second row.I think the above sql will fetch MAx(Y) as 2 which is 2nd row.Thanks |
|
| Back to top |
|
|
danm
Intermediate
Joined: 29 Jun 2004
Posts: 170
Topics: 73
|
| Posted: Mon Apr 20, 2009 9:41 am Post subject: |
|
|
Try:
SELECT X, MAX(Y) FROM TABLE
WHERE X IN(SELECT X FROM TABLE
WHERE Z = '2001-01-01')
GROUP BY X |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Apr 20, 2009 10:19 am Post subject: |
|
|
maverick05,
What makes you think my sql will fetch the second row? Did you even try to run?
Danm ,
Isn't it the same sql I have shown?
Kolusu |
|
| Back to top |
|
|
danm
Intermediate
Joined: 29 Jun 2004
Posts: 170
Topics: 73
|
| Posted: Mon Apr 20, 2009 11:17 am Post subject: |
|
|
Kolusu,
Opps! It is the same. |
|
| Back to top |
|
|
maverick05
Beginner
Joined: 15 May 2005
Posts: 72
Topics: 24
|
| Posted: Mon Apr 20, 2009 1:04 pm Post subject: |
|
|
| Thanks kolusu.it worked |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
