• 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

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths