Mysql is sorted by column when not empty and then ordered by another column

Question

I like some help that made me think for two days. I need to restore data from a database and order its column1 when it is not empty, and then the rest of the result on column2

 column1 column2 1 11 2 12 3 13 14 15 16 

Desired Result

 1,2,3,14,15,16 

I tried many approaches, my last unsuccessful attempt

$SQL = "SELECT * FROM table ORDER BY COALESCE(column1,column2) DESC";

and

`$SQL = "SELECT * FROM table ORDER BY COALESCE(column1,column2) ASC";

My above SQL returns the NULL column value of column1 above column2

Answer 1

This should work:

 $SQL = "SELECT * FROM table ORDER BY COALESCE(NULLIF(Other,''), column2) DESC"; 

I saw it here: SQL Coalesce with an empty string

Answer 2

coalesce() will only work if the "empty" values ​​in column 1 are actually NULL . Empty lines will not trigger the coalesce() operation.

In addition, your request will NOT work. You cannot make select * with two columns and somehow magically get one column as a result. To do this, you will need a UNION query:

 (SELECT column1 AS col FROM yourtable) UNION ALL (SELECT column2 AS col FROM yourtable) ORDER BY col 

Answer 3

You can use CASE like this:

 SELECT * FROM table ORDER BY CASE WHEN LENGTH(column1) IS >0 THEN column1 ELSE column2 END ASC 

http://dev.mysql.com/doc/refman/5.0/en/case.html

Answer 4

If you need 1 column, you can try a combination of NULLIF and COALESCE , which should take into account both empty and zero values

 SELECT COALESCE(NULLIF(column1, ''), column2) AS COL FROM table 

SQLFiddle Demo

If you really want all numbers on the same line of the result to be separated by commas, you can use GROUP_CONCAT along with the previous code:

 SELECT GROUP_CONCAT(COALESCE(NULLIF(column1, ''), column2)) AS col FROM table 

SQLFiddle Demo2

Answer 5

Old question, but this solution worked for me:

$SQL = "SELECT * FROM table ORDER BY COALESCE(NULLIF(column1, ''), column2)";