Query: select latest date within given category

[martinhough]

Hi

A basic one I think but stumping my limited knowledge;-

I need to run a query from 3 tables. 'Detail', 'RiskAssessments' and 'Subject Area' where I only need the latest date of the repspective 'Detail' entry.

My SQL currently looks like this;-

SELECT RiskAssessments.DateofRA, Detail.Detail, RiskAssessments.PSR, RiskAssessments.PLR, [detail.psr]*[revised plr] AS RRN
FROM ([Subject Area] INNER JOIN Detail ON [Subject Area].ID = Detail.[Subject Area]) LEFT JOIN RiskAssessments ON Detail.ID = RiskAssessments.DetailID
GROUP BY RiskAssessments.DateofRA, [Subject Area].SubjectArea, Detail.Detail, RiskAssessments.PSR, RiskAssessments.PLR, [detail.psr]*[revised plr]
HAVING ((([detail.psr]*[revised plr])>8));

This returns all instances (previous to the latest date) of each 'Detail' which meets the requirements of 'RRN' being greater than 8; I only require the latest of each group ('Detail') that meets this.

Any help much appreciated.

Thanks

[Dal Jeanis]

So, call that whole thing Query1 and wrap it by

SELECT 
   Q1.Detail,
   Q1.DateofRA, 
   Q1.PSR, 
   Q1.PLR, 
   Q1.RRN
FROM Query1   
WHERE Q1.DateofRA = 
    (SELECT MAX(Q2.DateofRA)
     From Query1 AS Q2
     WHERE Q1.Detail = Q2.Detail);

[martinhough]

See, told you it was easy!

Ha, ha. Seriously - thank you very much - I've learnt something there. Brilliant.

[Dal Jeanis]

Hopefully that means it worked. These kinds of correlated subquery are notoriously "fiddly".

[martinhough]

Yes indeed Dal - Thank you - works a treat!