[SOLVED] ORDER BY starting from '1'

[ether]

SELECT f.`frid` , f.`order` , f.`friendId` , u.`usLogin`

FROM `friends` AS f

LEFT JOIN `users` AS u ON f.`friendId` = u.`usId`

WHERE f.`usId` = '5'

ORDER BY f.`order` ASC

LIMIT 0 , 30

 

This returns everything correctly, however. The field that I'm ordering by, `order`, has a NULL value by default so it brings ALL of the NULL values first, and then orders ASC on `order`.

 

e.g.

 

NULL

NULL

NULL

1

2

3

 

I know I can add to my WHERE clause with something like "AND `order` != NULL" but I _want_ records with a NULL value to show up, but just to be sorted to the bottom of the list when ORDERing BY. So really I want the ORDERing to start with '1'...

 

I'm pretty sure I can use a PHP function, but I just cant seem to figure out which and I'd prefer to do it with SQL if possible.

 

Thanks!

[Wildbug]

There's a solution somewhere in the MySQL manual user comments.  It's something to the effect of using "... ORDER BY -order DESC" and will put the NULLs at the bottom of an ascending list.

[bubblegum.anarchy]

This is what I do:

 

SELECT * FROM repository
ORDER BY sort_column IS NULL, sort_column

[ether]

bubblegum.anarchy: Thanks! That seemed to of work