I have the following code in SQL Server and it is not quite doing what I hope. I want to filter the Code table by the values, then output only the most recent. I can do it as 2 queries but ideally want to do it all in one but can’t quite work out how to piece it all together

This is the code I’m using. I get a result that gives me the right values but doesn’t then give me the most recent

SELECT 
C.IDPatient,
C.DateEvent,
C.CTV3Code,
C.CTV3Text

FROM   Code C

left outer join (SELECT GSF.*,
           Row_number()
             OVER (
               partition BY PatientID
               ORDER BY Date DESC ) AS seqnum
            FROM   GSFStatus GSF ) GSF
            ON GSF.PatientID = C.IDPatient
            and seqnum = 1
WHERE (CTV3Code IN ('XaZb7', 'XaZbA', 'XaZbD', 'XaZbE'))