ORDER BY field ASC with NULL items at end

Question

I want to do an ORDER BY such that it would show the following:

- 1
- 3
- 5
- NULL
- NULL

If I do a ORDER BY field ASC, it would give me:

- NULL
- NULL
- 1
- 3
- 5

What would be the most straightforward way to do the first ordering?

Answer 1

you can try to do:

order by IFNULL(`field`, 18446744073709551615)

or select another max value for your type from http://dev.mysql.com/doc/refman/5.0/en/integer-types.html

Answer 2

Order by 2 things. First by the condition if the values are null and then by the column values

ORDER BY field is not null,
         field ASC

Answer 3

In my mind, the cleanest way in to place a minus sign (-) before the column name in your ORDER BY and change the ASC to DESC.

So: ORDER BY column_name ASC Becomes: ORDER BY -column_name DESC

I believe this is an undocumented feature.

Answer 4

Use the syntax: select value from num order by isnull(value), value;

To demonstrate:

create table nums (value int);
insert into nums values (1), (3), (5), (null), (null);
select value from nums order by isnull(value), value;

+-------+
| value |
+-------+
|     1 |
|     3 |
|     5 |
|  NULL |
|  NULL |
+-------+