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.
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