I do this a lot:
select a.* from myfile a join
(select client, max(date) from myfile group by client) b
on a.client=b.client and a.date = b.date
Note: subject to syntax errors, I just typed the example without running
it and kept it simple.
You should have an index on client and an index on date.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of David FOXWELL
Sent: Tuesday, December 01, 2009 4:24 AM
To: Midrange Systems Technical Discussion
Subject: SQL extract a row from a set of rows
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.
midrange.com
