RE: SQL question: return the last record of groups

["Reinardy, James" <jreinardy@xxxxxxxxxxxxxxx>]

Reggie,

How about something like:

Select Order_Number, Audit_date,... from Order_Audit join (Select
Order_Number, Max(Audit_date) as MaxAuditDate from Order_Audit group by
Order_Number) as MaxAuditView on Order_Audit.Order_Number
MaxAuditView.Order_Number = Order_Audit.Audit_date =
MaxAuditView.MaxAuditDate

Jim

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Reggie Acosta
Sent: Thursday, June 09, 2005 3:59 PM
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