Re: SQL extract a row from a set of rows

[Luis Rodriguez <luisro58@xxxxxxxxx>]

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.