mysql query

drr37

I am doing a mysql query which sorts data alphabetically by a codename, ie. "Select * from Projects Order by Codename". Since not all Projects have Codenames, how do I get the ones without Codenames to come up after the ones with Codenames, still sorting the Codenames alphabetically?

Anon

select * from projects
order by case when codename is null then 1 else 0 end , codename

sarahk

Don't know the mysql syntax but in vfp I'd do this:

select codename, iif(empty(codename),'B','A') as sortorder, *
from projects
order by sortorder, codename

the projects with a codename are given 'A' so they automatically go to the top.

By the same logic you could also have

iif(empty(codename),projectname, codename) as sortorder and then it would just use the project name if codename wasn't given.

REMEMBER TO CHECK MY SYNTAX!

sarahk

OK Dave - you've got the syntax for my suggestion and a better way of doing it!

I hadn't realised that MySql used the "case when" thing. Thanks Lars!

drr37

thanks, it worked
I did have to change it to "Codename = ''" since the field is "not null".