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

aggregatefirebirdwindow functions

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.

Best Answer

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;
Related Question