Select PropertyStatusDate
  ,comments
  ,rmVvlPropertyStatus
  ,rvlPropertyStatus.rvlpropertyid
  ,rvlPropertyStatusID
From dbo.rvlPropertyStatus Inner Join
	(Select Max(PropertyStatusDate) As maxDate
	   ,rvlPropertyID
	 From dbo.rvlPropertyStatus
	 Group By rvlPropertyID) As maxStatus
  On rvlPropertyStatus.PropertyStatusDate = maxStatus.maxDate
    And rvlPropertyStatus.rvlPropertyID = maxStatus.rvlPropertyID

Exec dbo.upObjectQuickDrop
  @ObjectName = 'TempDataHolder'
  ,@ObjectType = 'Table'
  ,@Schema = 'TempDB';
Go

With CTE
	(NestRowNumber
	 ,rvlPropertyID)
As
	(Select Row_Number() Over(Order By rvlPropertyID)
	   ,rvlPropertyID
	 From dbo.rvlPropertyStatus)
Select Max(NestRowNumber) As RowNumber
  ,rvlPropertyID
Into #TempDataHolder
From CTE
Group By rvlPropertyID
Order By rvlPropertyID;
Go

Exec dbo.upObjectQuickDrop
  @ObjectName = 'TempResultsSet'
  ,@ObjectType = 'Table'
  ,@Schema = 'TempDB';
Go

Select (Row_Number() Over(Order By rvlPropertyID)) As RowNumber
  ,PropertyStatusDate
  ,comments
  ,rmVvlPropertyStatus
  ,rvlPropertyStatus.rvlpropertyid
  ,rvlPropertyStatusID
Into #TempResultsSet
From dbo.rvlPropertyStatus

Select TRS.RowNumber
  ,PropertyStatusDate
  ,comments
  ,rmVvlPropertyStatus
  ,TRS.rvlPropertyID
  ,rvlPropertyStatusID
From #TempResultsSet TRS Inner Join #TempDataHolder TDH
  On TRS.rvlPropertyID = TDH.rvlPropertyID
    And TRS.RowNumber = TDH.RowNumber

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