Search code examples
sqlpostgresqlsql-order-by

Order by Column1 value if it is not null, otherwise use Column2 value

In SQL, is there a way to combine ORDER BY and IS NULL so that I can order by the value in column 1 if it is not null; and if it is null, order by the value in another column?

Solution

  • You can use case expression:

    ORDER BY CASE
    WHEN Column1 IS NOT NULL THEN Column1
    ELSE Column2
END

    Or the corresponding syntactic sugar:

    ORDER BY COALESCE(Column1, Column2)

    The result will be like:

    column1 | column2
--------|--------
      1 |    1258
      2 |    5972
   null |       3
      4 |    3698
      5 |    7524

    Note that the datatype of the two columns must be comparable (the RDBMS defines the rules).