Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

MySQL sorting problem with null values

Codes here !

Moderators: macek, egami, gesf

MySQL sorting problem with null values

Postby 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 :?:
EJDiaz
New php-forum User
New php-forum User
 
Posts: 2
Joined: Tue Feb 04, 2003 4:46 am

Postby 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
Elena Mitovska
New php-forum User
New php-forum User
 
Posts: 13
Joined: Tue Nov 12, 2002 12:11 pm
Location: Ukraine

Thanks

Postby EJDiaz » Wed Feb 05, 2003 1:38 am

You made my day! :D
Thanks
EJDiaz
New php-forum User
New php-forum User
 
Posts: 2
Joined: Tue Feb 04, 2003 4:46 am


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 2 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.