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

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.

CodePudding user response：

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