Re: Select by priority - Mailing list pgsql-sql

From Peter Eisentraut
Subject Re: Select by priority
Date
Msg-id Pine.LNX.4.21.0007160454440.379-100000@localhost.localdomain
Whole thread Raw
In response to Select by priority  (kumar1@home.com (Prasanth A. Kumar))
List pgsql-sql
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@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden



pgsql-sql by date:

Previous
From: kumar1@home.com (Prasanth A. Kumar)
Date:
Subject: Re: Select by priority
Next
From: "Robert B. Easter"
Date:
Subject: How to get count of rows in cursor