MySQL sorting problem with null values

by **EJDiaz** » Tue Feb 04, 2003 5:08 am

I have the following table:

Code: Select all: +----------+------------+ | username | last_order | +----------+------------+ | Buffy | NULL | | Bowser | 1999-10-01 | | Fang | NULL | | Slim | 2000-02-28 | | Puffball | 1999-03-30 | +----------+------------+

I want to sort it by last order so that

Code: Select all: SELECT username, last_order FROM table ORDER BY last_order ASC

Returns:

Code: Select all: +----------+------------+ | username | last_order | +----------+------------+ | Buffy | NULL | | Fang | NULL | | Puffball | 1999-03-30 | | Bowser | 1999-10-01 | | Slim | 2000-02-28 | +----------+------------+

But, I want to sort the table so that NULL values will be shown last in the ascending (ASC):

Code: Select all: +----------+------------+ | username | last_order | +----------+------------+ | Puffball | 1999-03-30 | | Bowser | 1999-10-01 | | Slim | 2000-02-28 | | Buffy | NULL | | Fang | NULL | +----------+------------+

or in descending (DESC) order

Code: Select all: +----------+------------+ | username | last_order | +----------+------------+ | Slim | 2000-02-28 | | Bowser | 1999-10-01 | | Puffball | 1999-03-30 | | Buffy | NULL | | Fang | NULL | +----------+------------+

What do I need to modify in the SQL statement to do this

:

by **Elena Mitovska** » Tue Feb 04, 2003 11:11 pm

You need to write SQL querylike this:

SELECT username, last_order FROM table ORDER BY
isNULL(last_order), last_order

It will return:

Puffball 1999-03-30
Bowser 1999-10-01
Slim 2000-02-28
Buffy NULL
Fang NULL

Or like this to get the result in descending order:

SELECT username, last_order FROM table ORDER BY
isNULL(last_order), last_order desc

Slim 2000-02-28
Bowser 1999-10-01
Puffball 1999-03-30
Buffy NULL
Fang NULL

by **EJDiaz** » Wed Feb 05, 2003 1:38 am

You made my day!

Thanks