MySQL Orderby a number, Nulls last

Question

Currently I am doing a very basic OrderBy in my statement.

SELECT * FROM tablename WHERE visible=1 ORDER BY position ASC, id DESC

The problem with this is that NULL entries for 'position' are treated as 0. Therefore all entries with position as NULL appear before those with 1,2,3,4. eg:

NULL, NULL, NULL, 1, 2, 3, 4

Is there a way to achieve the following ordering:

1, 2, 3, 4, NULL, NULL, NULL.

Answer 1

I found this to be a good solution for the most part:

SELECT * FROM table ORDER BY ISNULL(field), field ASC;

Answer 2

Why don't you order by NULLS LAST?

SELECT * 
FROM tablename
WHERE visible = 1 
ORDER BY position ASC NULLS LAST, id DESC 

Answer 3

For a DATE column you can use:

---

NULLS last:

ORDER BY IFNULL(`myDate`, '9999-12-31') ASC

Blanks last:

ORDER BY IF(`myDate` = '', '9999-12-31', `myDate`) ASC

Answer 4

Something like

SELECT * FROM tablename where visible=1 ORDER BY COALESCE(position, 999999999) ASC, id DESC

Replace 999999999 with what ever the max value for the field is

Answer 5

Try using this query:

SELECT * FROM tablename
WHERE visible=1 
ORDER BY 
CASE WHEN position IS NULL THEN 1 ELSE 0 END ASC,id DESC

Answer 6

SELECT * FROM tablename WHERE visible=1 ORDER BY CASE WHEN `position` = 0 THEN 'a' END , position ASC