基於最大日期而不是 NULL 的多行結果集

員工更改了他們的個人資料，如下表所示：

最新資訊的結果應該是這樣的：

我將此問題中的技術應用於LibreOffice Base 的嵌入式 Firebird，查詢有錯誤。

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;

引用自：https://dba.stackexchange.com/questions/279568