CODEWITHSUNDEEP
mysql
David542
David542

Reputation: 110257

ORDER BY field ASC with NULL items at end

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?

Upvotes: 1

Views: 67

Answers (4)

juergen d
juergen d

Reputation: 204766

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

Upvotes: 2

Robert Bain
Robert Bain

Reputation: 9586

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.

Upvotes: 0

anupam.singhal
anupam.singhal

Reputation: 101

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 |
+-------+

Upvotes: 0

Iłya Bursov
Iłya Bursov

Reputation: 24146

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

Upvotes: 3

Related Questions