pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect by priority
6+ messages / 4 participants
[nested] [flat]
* Select by priority
@ 2000-07-15 17:11 Prasanth A. Kumar <kumar1@home.com>
0 siblings, 1 reply; 6+ messages in thread
From: kumar1@home.com @ 2000-07-15 17:11 UTC (permalink / raw)
To: pgsql-sql
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?
--
Prasanth Kumar
kumar1@home.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Select by priority
@ 2000-07-16 00:51 omid omoomi <oomoomi@hotmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: omid omoomi @ 2000-07-16 00:51 UTC (permalink / raw)
To: kumar1@home.com; pgsql-sql
Hi,
How about ordering by Address_type ? only needs to have 1, 2, 3 as
different address types in your desired order. like :
select person_id , address where address in not null order by address_type
limit 1 ;
sure you can change the limit if you wish more addresses.
hope that helps
Omid Omoomi
>From: kumar1@home.com (Prasanth A. Kumar)
>To: pgsql-sql@postgresql.org
>Subject: [SQL] Select by priority
>Date: 15 Jul 2000 10:11:43 -0700
>
>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?
>
>--
>Prasanth Kumar
>kumar1@home.com
________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Select by priority
@ 2000-07-16 08:15 Prasanth A. Kumar <kumar1@home.com>
parent: omid omoomi <oomoomi@hotmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: kumar1@home.com @ 2000-07-16 08:15 UTC (permalink / raw)
To: pgsql-sql
"omid omoomi" <oomoomi@hotmail.com> writes:
> Hi,
> How about ordering by Address_type ? only needs to have 1, 2, 3 as
> different address types in your desired order. like :
>
> select person_id , address where address in not null order by address_type
> limit 1 ;
>
> sure you can change the limit if you wish more addresses.
> hope that helps
> Omid Omoomi
>
> >From: kumar1@home.com (Prasanth A. Kumar)
> >To: pgsql-sql@postgresql.org
> >Subject: [SQL] Select by priority
> >Date: 15 Jul 2000 10:11:43 -0700
> >
> >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?
<snip>
I think the order thing will work though I have no choice of using
numbering for the address_type as I am working off a pre-existing
database. They are using a mnemonic char type. I am essentially batch
downloading and processing this and other database tables for query
and presentations using web pages. BTW, does the 'limit' feature exist
in Oracle? The main database I am extracting data from is Oracle...
--
Prasanth Kumar
kumar1@home.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Select by priority
@ 2000-07-16 14:45 Peter Eisentraut <peter_e@gmx.net>
parent: Prasanth A. Kumar <kumar1@home.com>
0 siblings, 0 replies; 6+ messages in thread
From: Peter Eisentraut @ 2000-07-16 14:45 UTC (permalink / raw)
To: Prasanth A. Kumar <kumar1@home.com>; +Cc: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: Select by priority
@ 2000-07-17 22:12 Gary J. Farmer <farmer@arlut.utexas.edu>
parent: Prasanth A. Kumar <kumar1@home.com>
0 siblings, 1 reply; 6+ messages in thread
From: Gary J. Farmer @ 2000-07-17 22:12 UTC (permalink / raw)
To: pgsql-sql
>> I think the order thing will work though I have no choice of using
>> numbering for the address_type as I am working off a pre-existing
>> database. They are using a mnemonic char type. I am essentially batch
>> downloading and processing this and other database tables for query
>> and presentations using web pages. BTW, does the 'limit' feature exist
>> in Oracle? The main database I am extracting data from is Oracle...
I do not know whether you can add a table associating "priority" with
"address_type". If you can, you might try something like the following
example, using the address_priority table:
create table address_table (
person_id integer,
address_type varchar(1),
address varchar(50)
);
insert into address_table values ( 1, 'W', 'ROUTE 1, WORK ST');
insert into address_table values ( 2, 'H', 'ROUTE 2, HOME AVE');
insert into address_table values ( 3, 'W', 'ROUTE 3, WORK ST');
insert into address_table values ( 3, 'H', 'ROUTE 3, HOME AVE');
insert into address_table values ( 4, 'M', 'ROUTE 4, MAIL RD');
insert into address_table values ( 5, 'M', 'ROUTE 5, MAIL RD');
insert into address_table values ( 5, 'W', 'ROUTE 5, WORK ST');
insert into address_table values ( 6, 'M', 'ROUTE 6, MAIL RD');
insert into address_table values ( 6, 'H', 'ROUTE 6, HOME AVE');
insert into address_table values ( 7, 'M', 'ROUTE 7, MAIL RD');
insert into address_table values ( 7, 'H', 'ROUTE 7, HOME AVE');
insert into address_table values ( 7, 'W', 'ROUTE 7, WORK ST');
create table address_priority (
address_type varchar(1),
priority integer
);
insert into address_priority values ( 'M', 1 );
insert into address_priority values ( 'H', 2 );
insert into address_priority values ( 'W', 3 );
select person_id, address
from address_table a, address_priority b
where
(person_id, priority) in
(select person_id, min(priority)
from address_table a, address_priority b
where a.address_type = b.address_type
group by person_id) and
a.address_type = b.address_type;
PERSON_ID ADDRESS
---------- --------------------------------------------------
1 ROUTE 1, WORK ST
2 ROUTE 2, HOME AVE
3 ROUTE 3, HOME AVE
4 ROUTE 4, MAIL RD
5 ROUTE 5, MAIL RD
6 ROUTE 6, MAIL RD
7 ROUTE 7, MAIL RD
Appears to work with either Oracle or Postgres (though I changed VARCHAR
to VARCHAR2 for Oracle).
Gary Farmer
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Select by priority
@ 2000-07-18 02:37 Prasanth A. Kumar <kumar1@home.com>
parent: Gary J. Farmer <farmer@arlut.utexas.edu>
0 siblings, 0 replies; 6+ messages in thread
From: kumar1@home.com @ 2000-07-18 02:37 UTC (permalink / raw)
To: pgsql-sql
"Gary J. Farmer" <farmer@arlut.utexas.edu> writes:
> >> I think the order thing will work though I have no choice of using
> >> numbering for the address_type as I am working off a pre-existing
> >> database. They are using a mnemonic char type. I am essentially batch
> >> downloading and processing this and other database tables for query
> >> and presentations using web pages. BTW, does the 'limit' feature exist
> >> in Oracle? The main database I am extracting data from is Oracle...
>
> I do not know whether you can add a table associating "priority" with
> "address_type". If you can, you might try something like the following
> example, using the address_priority table:
<snip>
> Appears to work with either Oracle or Postgres (though I changed VARCHAR
> to VARCHAR2 for Oracle).
>
> Gary Farmer
That looks like a workable solution. I'll try it out. Thanks...
--
Prasanth Kumar
kumar1@home.com
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2000-07-18 02:37 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-15 17:11 Select by priority Prasanth A. Kumar <kumar1@home.com>
2000-07-16 14:45 ` Peter Eisentraut <peter_e@gmx.net>
2000-07-16 00:51 Re: Select by priority omid omoomi <oomoomi@hotmail.com>
2000-07-16 08:15 ` Prasanth A. Kumar <kumar1@home.com>
2000-07-17 22:12 ` Gary J. Farmer <farmer@arlut.utexas.edu>
2000-07-18 02:37 ` Prasanth A. Kumar <kumar1@home.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox