TSQL First Non-Null value in Audit Table

Question

I have a table in SQL Server 2014 that has the following data:

ID  ChangeDate               FName   MName  LName   Revision
1   2016-01-03 00:00:00.000  Ed      NULL   NULL    3
1   2016-01-02 00:00:00.000  NULL    David  NULL    2
1   2016-01-01 00:00:00.000  Joe     NULL   NULL    1   
2   2016-01-03 00:00:00.000  Michael NULL   NULL    2
2   2016-01-02 00:00:00.000  Henry   Jake   Smith   1

I need a tsql query that generates a single row with the most current output for each column:

ID FName    MName   LName
1  Ed       David   NULL 
2  Michael  Jake    Smith

Answer 1

WITH
    sorted AS
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY id
                               ORDER BY CASE WHEN FName IS NULL THEN 1 ELSE 0 END,
                                        Revision DESC
                          ) AS FNameOrdinal,
        ROW_NUMBER() OVER (PARTITION BY id
                               ORDER BY CASE WHEN MName IS NULL THEN 1 ELSE 0 END,
                                        Revision DESC
                          ) AS MNameOrdinal,
        ROW_NUMBER() OVER (PARTITION BY id
                               ORDER BY CASE WHEN LName IS NULL THEN 1 ELSE 0 END,
                                        Revision DESC
                          ) AS LNameOrdinal,
        *
    FROM
        yourTable
)
SELECT
    id,
    MAX(CASE WHEN FNameOrdinal = 1 THEN FName ELSE NULL END)   AS FName,
    MAX(CASE WHEN MNameOrdinal = 1 THEN MName ELSE NULL END)   AS MName,
    MAX(CASE WHEN LNameOrdinal = 1 THEN LName ELSE NULL END)   AS LName
FROM
    sorted
GROUP BY
    id