Order By

[AllenGarner]

I need to be able to order results based on several criteria.
I need to be able select all records where field:group = 'value given'
and display results sorted by these criteria
1) if field:company is not empty then display field:company asc
2) if field:company is empty then display field:fullname asc if not empty
3) if field:company and field:fullname are empty then display field:lastname asc if not empty
4)if field:company and field:fullname and field:lastname are empty display field:firstname if not empty

these results would be easy if I could just sort all by say company but if some fields in the group have an empty company field then it doesn't get included how can I sort by all these criteria. how could I get all the records in group sorted if some fields will be empty and others not
Any Ideas? My Brain is fried now going into overload can anyone help?

 

[hvass]

Not pretty but what about creating a custom field to sort by ...

select
  company,
  fullname,
  lastname,
  firstname,
  if(company>"",company,
  if(fullname>"",concat("_",fullname),
  if(lastname>"",concat("__",lastname),
  if(firstname>"",concat("___",firstname),"____"))) as sortfield
from mytable
order by sortfield;

 

[TonyGroves]

Another solution you could use:
[tt]
select company,fullname,lastname,firstname
from mytable
order by greatest(company,fullname,lastname,firstname)
[/tt]

 

[TonyGroves]

Sorry, my solution won't do what you want. Back to the drawing board, or to hvass's solution.

 

[TonyGroves]

Here's a possible solution:
[tt]
select company,fullname,lastname,firstname
from mytable
order by
greatest(
trim(company),
concat(' ',trim(fullname)),
concat(' ',trim(lastname)),
concat(' ',trim(firstname))
)
[/tt]
If you can assume that none of the fields start with a space, you could simplify it:
[tt]
select company,fullname,lastname,firstname
from mytable
order by
greatest(
company,
concat(' ',fullname),
concat(' ',lastname),
concat(' ',firstname)
)
[/tt]
I hope I have it right this time!

 

[AllenGarner]

Thanks guys, I found a solution. I just selected all into an array and did some nifty array work. All is well, thanks for your help.