Received: from ns2.arlut.utexas.edu (ns2.arlut.utexas.edu [129.116.224.3]) by hub.org (8.10.1/8.10.1) with ESMTP id e6HM2w013841 for ; Mon, 17 Jul 2000 18:02:58 -0400 (EDT) Received: from ns5.arlut.utexas.edu (ns5.arlut.utexas.edu [10.4.1.6]) by ns2.arlut.utexas.edu (8.9.3/8.9.1) with ESMTP id RAA11278 for ; Mon, 17 Jul 2000 17:02:57 -0500 (CDT) Received: from geordi (geordi.arlut.utexas.edu [129.116.144.60]) by ns5.arlut.utexas.edu (8.9.3/8.9.3) with SMTP id RAA15812 for ; Mon, 17 Jul 2000 17:02:57 -0500 (CDT) From: "Gary J. Farmer" To: Subject: RE: Select by priority Date: Mon, 17 Jul 2000 17:12:54 -0500 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 Importance: Normal In-reply-to: X-Archive-Number: 200007/170 >> 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