Re: SQL extract a row from a set of rows

David,

If you have a key on the date field, why not just use that in a where
clause, instead of MIN? I mean, in your CTE, just specify with CLIENT_DATE =
-99999999 or whatever your special value for date is.

Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Tue, Dec 1, 2009 at 5:53 AM, David FOXWELL <David.FOXWELL@xxxxxxxxx>wrote:

Hi,

A result of an sql command is returning multiple rows from a transaction
file like this :

Client Date Amount
Client1 79899898 100
Client1 79909898 10
Client1 79919898 100
Client1 79929898 900

How can I get the amount from the first row? This corresponds to the last
entry, as the date field is numeric and equal to 99999999 - date field. I
have a key on this field, but no way of joining to it from my client file.

At the moment, I'm running a WITH T As ( ) kind of expression on the
transaction file to get the minimum date value for a client and then joining
to that.

This makes for a very long execution time. Surely there's a better way?

Thanks
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.