You could read about windowing function and learn how ti use them.
Your example can be solved like this:
SELECT ss.*
FROM
(
Select s.PropertyStatusDate
,s.comments
,s.rmVvlPropertyStatus
,s.rvlPropertyStatus.rvlpropertyid
,s.rvlPropertyStatusID
,rnum = ROW_NUMBER() OVER(PARTITION BY s.rvlPropertyID ORDER BY s.PropertyStatusDate DESC)
From dbo.rvlPropertyStatus s
) ss
WHERE ss.rnum = 1
Please use aliases.
Kind regards,
Vedran