mySQL order by has image AND id-CodePudding

I have a table with image column. In that column - the values are the image URLs. Now I want to get all the rows from the table in the following order: first - the rows with images, ordered by id. second - the rows without images, ordered by id.

I tried this code (I found it somewhere):

ORDER BY CASE WHEN image IS NULL THEN 0 ELSE 1 END, id DESC

but it doesn't work.

can you help me with it?

Thanks!

CodePudding user response：

change 0 an 1:

ORDER BY CASE WHEN image IS NULL THEN 1 ELSE 0 END, id DESC

or add DESC for the first order

ORDER BY CASE WHEN image IS NULL THEN 0 ELSE 1 END DESC, id DESC

The default order is ASC and you must put the ORDER after each field. So in your case you sort ASC for the image field and only DESC for the field id

sample

MariaDB [bernd]> SELECT * FROM orderby;
 ----- ------ 
| id1 | id2  |
 ----- ------ 
|   1 |    1 |
|   2 |    2 |
|   3 |    3 |
|   4 |    4 |
|   1 |    2 |
|   1 |    3 |
 ----- ------ 
6 rows in set (0.05 sec)

MariaDB [bernd]> SELECT * FROM orderby ORDER BY id1, id2 DESC;
 ----- ------ 
| id1 | id2  |
 ----- ------ 
|   1 |    3 |
|   1 |    2 |
|   1 |    1 |
|   2 |    2 |
|   3 |    3 |
|   4 |    4 |
 ----- ------ 
6 rows in set (0.05 sec)

MariaDB [bernd]> SELECT * FROM orderby ORDER BY id1 DESC, id2 DESC;
 ----- ------ 
| id1 | id2  |
 ----- ------ 
|   4 |    4 |
|   3 |    3 |
|   2 |    2 |
|   1 |    3 |
|   1 |    2 |
|   1 |    1 |
 ----- ------ 
6 rows in set (0.00 sec)

MariaDB [bernd]>

CodePudding user response：

The boolean expression:

image IS NULL

is evaluated as 0 for false and 1 for true, so you don't need a CASE expression.

Also, if for the 2nd level of sorting you want the rows ordered by id, I assume that you want them in ascending order, so you should not use the keyword DESC.

Change to:

ORDER BY image IS NULL, id