CODEWITHSUNDEEP
sqlpostgresqlsql-order-by
1252748
1252748

Reputation: 15362

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?

Upvotes: 33

Views: 26285

Answers (4)

crassr3cords
crassr3cords

Reputation: 288

You could try with the following:

ORDER BY ISNULL(firstField, secondField)

Upvotes: -1

Salman Arshad
Salman Arshad

Reputation: 272006

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

Upvotes: 62

Nurickan
Nurickan

Reputation: 304

I dont have any Tables atm where I could test it, but this may work, at least it did without useable data:

SELECT * FROM table1
LEFT JOIN table2 ON table1.id = table2.id 
WHERE 1 
ORDER BY IF( table2.id, table1.id, table1.name )

Also I don't know how the order would look like if table2.id is null sometimes, seems very instable.

Upvotes: -2

vearutop
vearutop

Reputation: 4062

Try this 

  ORDER BY COALESCE(fieldA, fieldB);

Upvotes: 15

Related Questions