sql server row_number() count is zero

Question

I am joining to two tables one with a defect and the other with a defect status. The defect status table normally has multiple rows for each defect.

select d.defectID, d.defectName, ds.status
from (
      select d.defectID d.defectName, ds.status, 
              row_number() over (partition by ds.defectID order by ds.updateTimestamp desc) as rn 
      from defect d left join DefectStatus ds 
      on d.defectID=ds.defectID) as 
   T
where rn=1

The query works as long as a defect status exists. When a status record is not present the Defect record is not returned. Of course the left join is used but the where clause prevents it from being included in the result set. Please advise if there a better way to retrieve all the defect records with the status if any.

Answer 1

I think an OUTER APPLY provides a much cleaner query for what you are trying to achieve:

SELECT d.defectID, d.defectName, T.status
FROM Defect AS d
OUTER APPLY (
   SELECT TOP 1 ds.Status
   FROM DefectStatus AS ds
   WHERE d.defectID = ds.defectID
   ORDER BY ds.UpdateTimestamp DESC
) T

With the above query you get all records from Defect array joined by the most recent related record from DefectStatus, in case such record(s) exist.