Thread: Select by priority
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
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
"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
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
>> 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_priorityvalues ( 'W', 3 );
select person_id, address from address_table a, address_priority b where (person_id, priority) in
(selectperson_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
"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