Result set with multiple rows based on max date and not NULL

Question

Staff changed their profiles as shown in this table:

The result of latest information should be like this:

I applied the technique from this question for embedded Firebird of LibreOffice Base, the query has error.

Answer 1

SELECT DISTINCT StaffCode, 
       FIRST_VALUE(FirstName) 
           OVER (PARTITION BY StaffCode 
                 ORDER BY CASE WHEN FirstName IS NOT NULL 
                               THEN EffectiveDate 
                               END DESC) FirstName, 
       FIRST_VALUE(LastName)  
           OVER (PARTITION BY StaffCode  
                 ORDER BY CASE WHEN LastName IS NOT NULL  
                               THEN EffectiveDate 
                               END DESC) LastName, 
       FIRST_VALUE(MarriageStatus)  
           OVER (PARTITION BY StaffCode  
                 ORDER BY CASE WHEN MarriageStatus IS NOT NULL  
                               THEN EffectiveDate 
                               END DESC) MarriageStatus
FROM SourceTable;