Find first non-null values for multiple columns

Question

I'm attempting to get the first non-null value in a set of many columns. I'm aware that I could accomplish this using a sub-query per column. In the name of performance, which really does count in this scenario, I'd like to do this in a single pass.

Take the following example data:

col1     col2     col3     sortCol
====================================
NULL     4        8        1
1        NULL     0        2
5        7        NULL     3

My dream query would find the first non-null value in each of the data columns, sorted on the sortCol.

For example, when selecting the magical aggregate of the first three columns, sorted by the sortCol descending.

col1     col2     col3
========================
5        7         0

Or when sorting ascending:

col1     col2     col3
========================
1        4         8

Does anyone know a solution?

Answer 1

Have you actually performance tested this solution before rejecting it?

SELECT
    (SELECT TOP(1) col1 FROM Table1 WHERE col1 IS NOT NULL ORDER BY SortCol) AS col1,
    (SELECT TOP(1) col2 FROM Table1 WHERE col2 IS NOT NULL ORDER BY SortCol) AS col2,
    (SELECT TOP(1) col3 FROM Table1 WHERE col3 IS NOT NULL ORDER BY SortCol) AS col3

If this is slow it's probably because you don't have an appropriate index. What indexes do you have?