Without table DDL and sample data, we're just guessing.  However, this may be close to what you're looking for:

WITH NumberedbyMachine AS (
    SELECT
        RV.Name0,
        UCS.Status,
        UCS.LastStatusChangeTime,
        ROW_NUMBER() OVER (PARTITION BY Name0 ORDER BY 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 = 'COLLECTIONNAME'
    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,
  UCS.LastStatusChangeTime
FROM NumberedbyMachine
WHERE RowNo = 1

John