RE: SQL question: return the last record of groups

If your table has a sequence or ID field, you can do something like

Fields:
Ordernumber, seq, status
10, 1, open
10, 2, backorder
10, 3, shipped
10, 4, closed

SQL:
Select a.ordernumber, a.status from salesheader a
Where a.seq = (select max(z.seq) from salesheader z where
a.ordernumber=z.ordernumber)

Yields:
10, closed

This would give you each order number and status from the most recent
sequence number for each order.

This works similarly for date information, for instance, to get the most
recent action for an employee's history file.

HTH,

Loyd Goodbar
Senior programmer/analyst
BorgWarner
E/TS Water Valley
662-473-5713
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Reggie Acosta
Sent: Thursday, June 09, 2005 15:59
To: midrange-l@xxxxxxxxxxxx
Subject: SQL question: return the last record of groups

Would it be possible to form an SQL incantation that will return the last
record of the group of records for an entity? For example, I need to access
info that I'd normally get from the sales order header file. But, alas, some
orders have been completed and purged. Fortunately we have a sales order
header audit trail file. The last record for an order in the audit trail
would have the info needed. I'd need to get the last audit trail records for
all the purged sales orders. I beg the master SQL wizards, please share your
wisdom. :)

TIA,
Reggie Acosta
Brown Jordan International 
www.brownjordan.com