mysql sort table which contains null value

Posted: Μαΐου 30th, 2011 | Author: Giorgos | Filed under: programming | Tags: mysql, sql | No Comments »

Imagine the following table A:

Name Surname Code
Giorgos Komninos 3
Vassilis Antonopoulos NULL
Lazaros Papadopoulos 1

and now if we use the following sql command:

select * from A order by Code

the result would be:

Vassilis Antonopoulos NULL
Lazaros Papadopoulos 1
Giorgos Komninos 3

but we want those records with NULL Code to be in the end .

Here is the solution:

select * , Code is null as isnull from A order by isnull asc, Code asc

Then the result would be:

Lazaros Papadopoulos 1
Giorgos Komninos 3
Vassilis Antonopoulos NULL

---