Page 1 of 1

putting non-empty fields first then the empty ones - control

Posted: Wed Jul 21, 2004 2:07 am
by davidklonski
Hello

I have the following table:

Code: Select all

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:

Code: Select all

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

Posted: Wed Jul 21, 2004 4:44 am
by feyd
maybe a union?

Code: Select all

SELECT * FROM `people` WHERE `title` != '' AND `address` != '' ORDER BY `title`,`address`
UNION( SELECT * FROM `people` WHERE `title` = '' AND `address` != '' ORDER BY `address` )
UNION( SELECT * FROM `people` WHERE `title` != '' AND `address` = '' ORDER BY `title` )
UNION( SELECT * FROM `people` WHERE `title` = '' AND `address` = '' ORDER BY `ID` )
There's probably a way to do it with joins too; I just can't really think of the specifics..

Posted: Wed Jul 21, 2004 7:47 am
by davidklonski
this is too complicated
There must be a way of doing it with cases

Posted: Wed Jul 21, 2004 9:34 am
by JAM
You don't mention what version of MySQL you are using (<4.1, subqueries will be hard to manage) but perhaps the following might give you ideas:

Code: Select all

select 
	peeps.name,
	people.title,
	people.address
from 
	peeps
	inner join people on peeps.id = people.id
order by 
	isnull(people.title), 
	isnull(people.address)
Result:

Code: Select all

JAM	CEO	home
BAR	PR	somewhere
FOO 	PR 	NULL
BAR2 	NULL 	bofh
FOO2 	NULL 	NULL
I used a join to test against another table holding the names of the individuals. Rearrange the code to fit your needs if it helped.

Opps, just noticed; Note, that I have changed the titles/address field to allow NULL. If you wont be able to do that, use a string function that would produce the same result. using roughly the same approach.