SQL Count, group filter question

I am wanting to get the
First -  Unique AsbestosNumber 
Seconds - based on that the last survey date
Last - based on those filtered down results get the highest Risk (First look to see it is has 'A', then 'B' then 'C' then 'D' then anything else
So far the closets I have is
select AsbestosUPRN, OverallRiskCategory, SurveyDate FROM TblAsbestos GROUP BY AsbestosUPRN, OverallRiskCategory, SurveyDate but have also tried the below which ive of not be bound errors.

SELECT * FROM TblAsbestos JOIN (SELECT AsbestosUPRN, Max(OverallRiskCategory) FROM TblAsbestos) As Tbl2 On Tbl2.AsbestosUPRN = Tbl1.AsbestosUPRN ORDER BY Tbl1.AsbestosUPRN
I then need to modify the report to also search by a unique number in another table.