MySQL Orderby a number, Nulls last

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 swap out instances of NULL with a different value to sort them first (like 0 or -1) or last (a large number or a letter)...

SELECT field1, IF(field2 IS NULL, 9999, field2) as ordered_field2
  FROM tablename
 WHERE visible = 1
 ORDER BY ordered_field2 ASC, id DESC

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.)

To achieve following result :

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

USE syntax, place -(minus sign) before field name and use inverse order_type(Like: If you want order by ASC order then use DESC or if you want DESC order then use ASC)

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

This works well for me as well.

ORDER BY ISNULL(field), field = 0 ASC;

Returns 1 2 3 0 0 null null

That's simple. You just need to order twice:

• first step, order NULLs
• second step, order your field.

SELECT * FROM table_name 
ORDER BY ISNULL(field_name), field_name;

It works with any types, including JSON.

This is working fine:

SELECT * FROM tablename ORDER BY position = 0, position ASC;

position
1 
2
3
0
0