Order by null first, then order by other variable

Question

Hi, this is my code for now:

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

but I want it to look like:

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

What I want to do is to have all number that are NULL on the top of the result, but as soon number is not NULL, sort by id

Is that possible?

I use php and mysql.

Answer 1

what about something like this :

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

If number is NULL, the first order by criteria will be 0 ; else 1
Which means every line will number NULL will come before the others ones

And note that ids will be sorted too, anyway.

You'll get something like this :

• number null ; id=1
• number null ; id=2
• number null ; id=5
• number null ; id=8
• number not null ; id=3
• number not null ; id=4
• number not null ; id=7
• number not null ; id=10
• number not null ; id=12

Answer 2

Union could be used as well.

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;