Re: Select by priority

Prasanth A. Kumar writes:

> I need some suggestions on how to construct a particular select that I
> need. I have a table of addresses where the primary key is the
> persons_id and a address_type field. The address_type field is a
> character which specifies whether the address is for the home, work,
> or mailing. A person can have multiple addresses though only one of a
> type. What I want to do is select a list of address for each distinct
> individual but wish to select based of priority that if the mailing
> address exists, I get that one only. If there is no mailing address, I
> want the home address and failing that, get the work address. Is it
> within the realm of sql to be able to do that?

I'd work from this:

SELECT person_id, address, XXX(address_type)
FROM address_table GROUP BY address, person_id;

where XXX is an aggregate function that selects the highest "priority"
among the address_type codes. If your codes sort alphabetically or
numerically you can just use MIN or MAX, otherwise you'd have to write
your own, which shouldn't be terribly hard.

Instead of person_id you probably want to join against some person table,
but remember that you need to group by every non-aggregate column in the
select list.

--
Peter Eisentraut Sernanders väg 10:115
peter_e(at)gmx(dot)net 75262 Uppsala
http://yi.org/peter-e/ Sweden