Skip site navigation (1) Skip section navigation (2)

Re: Select by priority

From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: "Prasanth A(dot) Kumar" <kumar1(at)home(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Select by priority
Date: 2000-07-16 14:45:54
Message-ID: Pine.LNX.4.21.0007160454440.379-100000@localhost.localdomain (view raw, whole thread or download thread mbox)
Lists: 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(at)gmx(dot)net                   75262 Uppsala            Sweden

In response to

pgsql-sql by date

Next:From: Robert B. EasterDate: 2000-07-16 22:02:51
Subject: How to get count of rows in cursor
Previous:From: Prasanth A. KumarDate: 2000-07-16 08:15:30
Subject: Re: Select by priority

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group