Ordering of Numbers with NULL records

Hi,

I have a table of tinyints where these records can also be NULL.  I want to
return an ordered list of numbers, placing the NULL values at the end of the
list.  By default, MySQL doesn't do this, so I had to modify the query used.

See below:

mysql> create table test (number tinyint unsigned);
Query OK, 0 rows affected (0.00 sec)

mysql> describe test;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES  |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert test set number=8;
Query OK, 1 row affected (0.00 sec)

mysql> insert test set number=2;
Query OK, 1 row affected (0.00 sec)

mysql> insert test set number=66;
Query OK, 1 row affected (0.00 sec)

mysql> insert test set number=13;
Query OK, 1 row affected (0.00 sec)

mysql> insert test set number=null;
Query OK, 1 row affected (0.00 sec)

mysql> insert test set number=91;
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+--------+
| number |
+--------+
|      8 |
|      2 |
|     66 |
|     13 |
|   NULL |
|     91 |
+--------+
6 rows in set (0.01 sec)

mysql> select * from test order by number;
+--------+
| number |
+--------+
|   NULL |
|      2 |
|      8 |
|     13 |
|     66 |
|     91 |
+--------+
6 rows in set (0.00 sec)


This order is NOT what I want because all NULL values are first in the list,
so I tried the other queries below:


QUERY 1

mysql> select * from test order by number is null, number;
+--------+
| number |
+--------+
|      2 |
|      8 |
|     13 |
|     66 |
|     91 |
|   NULL |
+--------+
6 rows in set (0.00 sec)

QUERY 2

mysql> select * from test order by number is not null, number;
+--------+
| number |
+--------+
|   NULL |
|      2 |
|      8 |
|     13 |
|     66 |
|     91 |
+--------+
6 rows in set (0.00 sec)

QUERY 3

mysql> select * from test order by number, number is null;
+--------+
| number |
+--------+
|   NULL |
|      2 |
|      8 |
|     13 |
|     66 |
|     91 |
+--------+
6 rows in set (0.00 sec)

QUERY 4

mysql> select * from test order by number, number is not null;
+--------+
| number |
+--------+
|   NULL |
|      2 |
|      8 |
|     13 |
|     66 |
|     91 |
+--------+
6 rows in set (0.00 sec)


Query 1 appears to give the result I want (with null values being last in
the list and numbers being in order).  However, is this query actually
correct? - because I would read this as:

-> select all records from table test ordered first by records which ARE
NULL (eg, records which contain NO number) and then order by number (with
records which DO contain a number).  This would suggest that the result
should actually have NULL entires first, just like all the other results of
queries 2, 3 and 4 (or am I missing something?).

Can someone clarify what is going on here? (I'm not sure if this is a bug or
not).

Regards...

--
Mark


-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]