thnx john.
This actually worked.

WITH NumberedbyMachine AS (
  SELECT
   RV.Name0,
   UCS.Status,
   UCS.LastStatusChangeTime,
   ROW_NUMBER() OVER (PARTITION BY RV.Name0 ORDER BY UCS.LastStatusChangeTime DESC) AS RowNo
  FROM v_Update_ComplianceStatus UCS
  join v_R_System RV on UCS.ResourceID = RV.ResourceID
  join v_UpdateInfo UI on UCS.CI_ID = UI.CI_ID
  join v_UpdateDeploymentSummary UDS on UCS.CI_ID = UDS.CI_ID
  join v_GS_OPERATING_SYSTEM OS on RV.ResourceID = OS.ResourceID
  join v_FullCollectionMembership FCM on RV.ResourceID = FCM.ResourceID
  JOIN v_Collection Col on Col.CollectionID = FCM.CollectionID and Col.Name = 'MTN - H+H - Custom Compliance - HCLL Servers'
  join v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = RV.ResourceID
  )
SELECT
Name0,
Case Status
  when 0 then 'State unknown'
  When 1 then 'Not required'
  When 2 then 'Not Compliant'
  When 3 then 'Compliant'
  Else 'error'
End as UpdateStatus,
LastStatusChangeTime
FROM NumberedbyMachine
WHERE RowNo = 1


I've bolded the modifications I made in order to get it to work with my DDL.

I will try 

query next to see if I get better performance, and will continue to tinker around to make sure the information is along the way is correct.