RE: SQL question: return the last record of groups

I see an issue with my join clause, this should be: 

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 and Order_Audit.Audit_date =
MaxAuditView.MaxAuditDate

Not guaranteeing syntax here, I did not run this against real tables on
our system!

Jim

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Reinardy, James
Sent: Thursday, June 09, 2005 4:22 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL question: return the last record of groups

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


--
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.