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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

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

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

Post by davidklonski »

this is too complicated
There must be a way of doing it with cases
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
Post Reply