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

Question

Staff changed their profiles as shown in this table:

enter image description here

The result of latest information should be like this:

enter image description here

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;