[RESOLVED] ANOTHER SQL Question ...

[TheBionicOrange]

My SQL isn't that sharp, so I hope that there is an easy answer to this one.

The following grabs records from a table, and I format a few fields along the way. Thats all fine.

SELECT     a.BRANCH_NUMBER, b.EST_BRNM, a.DIVISION, a.REGION, a.REGIONAL_MGR, a.SAFETY_ADVISOR, SUBSTRING(Audit_Date, 7, 2) 
                      + '/' + SUBSTRING(Audit_Date, 5, 2) + '/' + SUBSTRING(Audit_Date, 1, 4) AS Audit_Date, a.WEIGHTED_RESULT, a.WEIGHTED_PERCENTAGE, 
                      SUBSTRING(Audit_Review_Date, 7, 2) + '/' + SUBSTRING(Audit_Review_Date, 5, 2) + '/' + SUBSTRING(Audit_Review_Date, 1, 4) AS Audit_Review_Date,
                       a.ACTION_PLAN_COMPLETE, SUBSTRING(Action_Plan_Completion_Date, 7, 2) + '/' + SUBSTRING(Action_Plan_Completion_Date, 5, 2) 
                      + '/' + SUBSTRING(Action_Plan_Completion_Date, 1, 4) AS Action_Plan_Completion_Date, a.RISK_INDICATOR, 
                      CASE RISK_INDICATOR WHEN 'R' THEN 'A' WHEN 'A' THEN 'B' WHEN 'G' THEN 'C' END AS SortCode
FROM         AUDIT_LOCATION_SUMMARY a INNER JOIN
                      ESTATES.DBO.BRANCH_DETAILS b ON (b.EST_CHAN = a.CHAIN AND b.EST_BRAN = a.BRANCH_NUMBER)
WHERE     a.CHAIN = '01'
ORDER BY 14 DESC

Now ... For each Branch there may be more than 1 record returned, based on "Audit_Date". I want all these fields, but only the LATEST date for each Branch, i.e. if more than 1 record exists for any Branch I only want the one with the latest date.

Bearing in mind I am using SUBSTRING to turn my dates around (they are stored as strings in the format YYYYMMDD), I am having trouble using the MAX command.

Am I barking up the wrong tree ?