SQL ORDER by `no` with NULLs at the end

Question

Hi!

I have got a MySql query that orders me results by no column (int, can be null). Simple example:

SELECT * FROM table ORDER BY no ASC

I would like to get a resultset sorted like

1, 2, 3, 10, 52, 66, NULL, NULL, NULL

but I get

NULL, NULL, NULL, 1, 2, 3, 10, 52, 66

Is it possible with SQL query ?

Answer 1

Ok, I think I got it:

SELECT * FROM table WHERE no IS NOT NULL ORDER BY no ASC UNION
SELECT * FROM table WHERE no IS NULL

Or is there any better way ?

Answer 2

SELECT * FROM table ORDER BY COALESCE(no,999999) ASC

Just replace the 999999 with something larger if your numbers are naturally bigger than that.

Answer 3

You can use a CASE statement to tweak ordering:

SELECT * 
FROM table 
ORDER BY case when no is null then 2 else 1 end, no

This orders on "nullableness" first, and no second.

Answer 4

Could you try this?

ORDER BY IF(ISNULL(no),1,0),no;