SQL Find the first non null value with arbitrary order by

Question

Given the following table:

ID | Value
----------
 1 | NULL
 2 |  B
 3 |  C
 4 | NULL

I want to get the first non-null value from the Value column according to different ordering, e.g.:

SELECT FIRST_NON_NULL(Value ORDER BY ID) FROM MY_TABLE

This will return B

SELECT FIRST_NON_NULL(Value ORDER BY ID DESC) FROM MY_TABLE

This will return C

P.S. Doesn't have to be a value function, just the SELECT statement that is needed. Thank you.

---

Edited: Want to see if there can be a scalable version.

ID | Value1 | Value2
--------------------
 1 |  NULL  |   AA
 2 |   B    |  NULL
 3 |   C    |   CC
 4 |  NULL  |  NULL

SELECT FIRST_NON_NULL(Value1, Value2 ORDER BY ID) FROM MY_TABLE

This will return B, AA

SELECT FIRST_NON_NULL(Value1, Value2 ORDER BY ID DESC) FROM MY_TABLE

This will return C, CC

Answer 1

Just do :

SELECT TOP 1 Value
FROM mytable
WHERE Value IS NOT NULL
ORDER BY ID

To get the last not null value switch the ordering:

ORDER BY ID DESC

To expand the same logic to a greater number of columns you can use the following query:

SELECT (SELECT TOP 1 Value1 FROM mytable 
        WHERE Value1 IS NOT NULL ORDER BY ID) AS min_Value1,
       (SELECT TOP 1 Value2 FROM mytable 
        WHERE Value2 IS NOT NULL ORDER BY ID) AS min_Value2

Demo here