Order by first non-null result that comes from two different columns

Question

I want to browse through all values of two columns in a table:

• if the value in column 1 is not null, select it, otherwise select the value in column 2 instead.
• then sort the final result in alphabetical ascending order, wherever column its values came from.

I tried the following query but it doesn't work and I'm not even sure it is supposed to do what I want to do.

SELECT * 
FROM table 
ORDER BY (CASE WHEN col1 IS NOT NULL THEN 1 ELSE 2 END ), 
         col1 DESC, 
         col2 DESC)

Besides the fact that it doesn't work (nothing outputted), it seems to sort the values of each column separately while I want to sort the final set of values retrieved, regardless of the column they are from.

Thank you for your help.

Answer 1

If you want to fix it with the CASE expression, it'd look like the following:

SELECT *, 
       CASE WHEN col1 IS NOT NULL 
            THEN col1 
            ELSE col2 
       END AS col
FROM table 
ORDER BY col

Although a nice option is using the COALESCE function. It returns the first non-null value in the list of arguments.

SELECT *, COALESCE(col1, col2) AS col
FROM table 
ORDER BY col