putting non-empty fields first then the empty ones - controling sort

[davidklonski]

Hello

I have the following table:

create table people (
  ID int(11) unsigned not null auto_increment primary key,
  title varchar(255) not null,
  address varchar(255) not null
);

I would like to list all the people sorted by first title and then address.
However some people may have a title while others may have an empty title.
The same is true for the address.

I would like to let SQL sort the people using Order by in the following manner:

order by title, address

The missnig requirement is that:
people who have a non-empty title should come before those with an empty title.
people who have a non-empty address should come before those with an empty address.

Is this possible?
I heard that this can be acheived using case when... but I am not sure

I would appreciate any help
thanks in advance

[mrhappiness]

ORDER BY
  LENGTH(title) != 0,
  title,
  LENGTH(name) != 0,
  name

try something like this

btw: my mysql does this ordering autmatically...

[davidklonski]

this didn't have any effect...
I am getting empty titles and then non-empty titles sorted alphabeticallyt

[mrhappiness]

you have 4 persons

person #1: no title no name
person #2: no title but name
person #3: title but no name
person #4: title and name

how do youwant these people to be listed?