Please consider this structure:
Id Year Month F1 F2 F3 F4
----------------------------------------------------------------------
1 2020 1 A B 1 2
1 2020 2 AA
1 2020 3 BB 11
2 2020 1 3 4
2 2020 2 F G
I want to have this result:
Id F1 F2 F3 F4
-----------------------------------------------
1 AA BB 11 2
2 F G 3 4
How can I have above result (get latest changes)?
Now I did it with a cursor and 4 variables and then I selected these variables. Is there a better way to do this?
Using FIRST_VALUE() with the appropriate OVER clause is a possible solution:
SELECT
DISTINCT Id,
F1 = FIRST_VALUE(F1) OVER (PARTITION BY Id ORDER BY IIF(F1 IS NULL or F1 ='', 1, 0), Year DESC, Month DESC),
F2 = FIRST_VALUE(F2) OVER (PARTITION BY Id ORDER BY IIF(F2 IS NULL or F2 ='', 1, 0), Year DESC, Month DESC),
F3 = FIRST_VALUE(F3) OVER (PARTITION BY Id ORDER BY IIF(F3 IS NULL or F3 ='', 1, 0), Year DESC, Month DESC),
F4 = FIRST_VALUE(F4) OVER (PARTITION BY Id ORDER BY IIF(F4 IS NULL or F4 ='', 1, 0), Year DESC, Month DESC)
FROM Data