Special order by

[benn600]

I have a table of users with userName and userFullName for my site http://giftpathways.com

UserName is simply their login username. UserFullName is an optional field that I have a lot of code throughout my site that checks to see if it is in use and if so, uses it to print things.

In sorting, this is very troubling. I thought order by userFullName,userName would work but it doesn't! It just places the two parts in order but the parts are not ordered against each other.

Is there a way to say order by which ever field has a value? Or another solution? Thanks!

[abduraooft]

Mysql Case (http://dev.mysql.com/doc/refman/5.0/en/case-statement.html)
SELECT field1,field2
FROM `users`
ORDER BY CASE WHEN `UserFullName` != ''
THEN UserFullName
ELSE userName
END
ASC

[guelphdad]

Use NULL as default in both fields. Then you merely need to use COALESCE

SELECT COALESCE(userfullname, username) as thename

if userfullname is NULL then username is used. this is quicker than using case in this instance.

[benn600]

Coalesce works great. thank you!

[ahallicks]

I'm using COALESCE for two date columns... one when the post was submitted, and another when the post was updated. If I use this on those two columns will the most up-to-date date automatically show if there is a date in each?

[Fumigator]

No...

RTFM (http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce)

[ahallicks]

Okay, I'll ask another question in that case. Is there a better way to do what I'm trying to do than COALESCE?

I have a query that displays a list of the content that has been submitted and I want the updated date to appear at the top if it is newest, or the submitted date if that is newest?

[Fumigator]

I would use "CASE" or "IF" in that situation.