MySQL Orderby a number, Nulls last

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.

 Answers

MySQL has an undocumented syntax to sort nulls last. Place a minus sign (-) before 

the column name and switch the ASC to DESC:

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

It is essentially the inverse of position DESC placing the NULL values last but 

otherwise the same as position ASC.

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

---

You can coalesce your NULLs in the ORDER BY statement:

select * from tablename
where <conditions>
order by
    coalesce(position, 0) ASC, 
    id DESC

If you want the NULLs to sort on the bottom, try coalesce(position, 100000). 

(Make the second number bigger than all of the other position's in the db.)

---

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

---

NULL LAST

SELECT * FROM table_name ORDER BY id IS NULL, id ASC