First we order null, then we order another variable

Question

this is my code:

SELECT id, number FROM Media WHERE user = 10 ORDER BY id, number 

but I want it to look like this:

 SELECT id, number FROM Media WHERE user = 10 ORDER BY while(number IS NULL), id 

I want all number be NULL at the top of the result, but as soon as number not NULL , sort by id

Is it possible?

I am using mysql.

Answer 1

how about this:

 SELECT id, number FROM Media WHERE user = 10 ORDER BY (case when number is null then 0 else 1 end), id 

If the number is NULL, the first order by criteria will be 0; 1 more
Which means that each row will be NULL will be earlier than others

And note that identifiers will also be sorted.

You will get something like this:

• number null; ID = 1
• number null; ID = 2
• number null; ID = 5
• number null; ID = 8
• the number is not zero; ID = 3
• the number is not zero; ID = 4
• the number is not zero; ID = 7
• the number is not zero; ID = 10
• the number is not zero; ID = 12

Answer 2

The ISNULL () function returns 1 if the parameter is zero and 0 otherwise.

 SELECT id, number FROM Media WHERE user = 10 ORDER BY ISNULL(number) DESC, id 

Keep in mind that this is terrible for performance depending on how many values ​​it needs to order: no index will be used based on ORDER BY columns.

Answer 3

You can use the union.

 SELECT id, number FROM Media WHERE user = 10 AND number IS NULL ORDER BY id UNION SELECT id, number FROM Media WHERE user = 10 AND number IS NOT NULL ORDER BY id, number;