Can we write a single query for this data?

maverick05 · New User Joined: 14 Apr 2005 Posts: 54 Location: Earth

Hi,

I have the following 3 rows in db2 table.Table has 3 columns X,Y,Z

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

[/img]

dick scherrer · Posted: Mon Apr 20, 2009 8:43 am

Hello,

What is the total number of rows in the entire table?

Are there any other rows with colX = A?

dbzTHEdinosauer · Posted: Mon Apr 20, 2009 3:56 pm

maverick05,

you posted this same question on another website; also received an answer there.

maverick05 · New User Joined: 14 Apr 2005 Posts: 54 Location: Earth

Hi,

There can be more rows with column X value as 'A'. Basically the query should check for one row(date check) and should fetch some other row(max Y)

I posted the same question in diff forums at the same time as i was needing the answer asap.

ashimer · Posted: Mon Apr 20, 2009 8:12 pm

So did you get your ans finally ?

dick scherrer · Posted: Mon Apr 20, 2009 8:38 pm

Hello,

If the question was as incomplete on the other forums, no telling what kind of answers might have been given. . .

Quite possibly something that will appear to work, but actually does not for all cases. . .

Far too many do not understand that it is critical that the requirement be well presented and not just "thrown together" because they are in a hurry. . .

dbzTHEdinosauer · Posted: Mon Apr 20, 2009 9:00 pm

The OP received an answer on the other site,
OP's response was I don't think that will work.

ashimer · Posted: Mon Apr 20, 2009 9:12 pm

Assuming column X will have values other than A .... one query would be

maverick05 · New User Joined: 14 Apr 2005 Posts: 54 Location: Earth

The qbove query worked.thanks alot

Terry Heinze · Posted: Tue Apr 21, 2009 8:02 am

maverick05, were you planning on letting folks know on BOTH boards that you found a solution on one of them, or were you planning on being inconsiderate of other peoples time?