Re: Stuck on analytics

From: Anthony Wilson <amwilson_at_iinet.net.au>
Date: Mon, 19 Feb 2007 09:52:57 +0900
Message-ID: <1171846377.45d8f4e98e4da@mail.iinet.net.au>

Hi,

How about something like this... no need for analytics:

  1 select dep.dep_id
  2 , min(com.line) keep (

  3     dense_rank first
  4     order by com.line asc

  5 ) com_line
  6 , min(mod.rec) keep (

  7     dense_rank first
  8     order by mod.rec asc

  9 ) mod_rec
 10 , min(loc.line) keep (

 11     dense_rank first
 12     order by loc.line asc

 13 ) loc_line
 14 from deposits dep

 15  , commodity com
 16  , model_type mod
 17  , locations loc

 18 where com.dep_id = dep.dep_id
 19 and mod.dep_id = dep.dep_id
 20 and loc.dep_id = dep.dep_id
 21* group by dep.dep_id
wrl2_at_DEV.ENVIRONMENT.LOCAL()> /

    DEP_ID COM_LINE MOD_REC LOC_LINE ---------- ---------- ---------- ----------   55555555 2 1 2

You can extend this to include any other columns you need from com, mod and loc. Could also use inline views with analytics but I like this approach better...

Hope that helps.

cheers,
Anthony

Quoting Bill Ferguson <wbfergus_at_gmail.com>:

> I've been stuck on this for almost two weeks now (and the RMOUG meeting
> didn't help).
> I have 4 tables (abbreviated) as follows:
>
> CREATE TABLE "DEPOSITS"
> ( "DEP_ID" NUMBER(12,0) NOT NULL ENABLE,
> "NAME" VARCHAR2(75 BYTE),
> "DEV_ST" VARCHAR2(25 BYTE) NOT NULL ENABLE,
> "OPER_TP" VARCHAR2(30 BYTE) NOT NULL ENABLE,
> "SIG" VARCHAR2(1 BYTE),
> CONSTRAINT "DEPOSITS_PK" PRIMARY KEY ("DEP_ID") ENABLE);
>
> CREATE TABLE "COMMODITY"
> ( "DEP_ID" NUMBER(12,0) NOT NULL ENABLE,
> "LINE" NUMBER(4,0) NOT NULL ENABLE,
> "CODE" VARCHAR2(6 BYTE) NOT NULL ENABLE,
> "COMMOD" VARCHAR2(25 BYTE) NOT NULL ENABLE,
> "QUAL" VARCHAR2(20 BYTE),
> "IMPORT" VARCHAR2(5 BYTE) NOT NULL ENABLE,
> CONSTRAINT "COMM_PK" PRIMARY KEY ("DEP_ID", "LINE") ENABLE,
> CONSTRAINT "COMM_FK" FOREIGN KEY ("DEP_ID")
> REFERENCES "DEPOSITS" ("DEP_ID") ON DELETE CASCADE ENABLE);
>
> CREATE TABLE "MODEL_TYPE"
> ( "DEP_ID" NUMBER(12,0) NOT NULL ENABLE,
> "REC" NUMBER(4,0) NOT NULL ENABLE,
> "USGS_NUM" VARCHAR2(15 BYTE) NOT NULL ENABLE,
> "MODEL_NAME" VARCHAR2(70 BYTE) NOT NULL ENABLE,
> CONSTRAINT "MODEL_TYPE_PK" PRIMARY KEY ("DEP_ID", "REC") ENABLE,
> CONSTRAINT "MODEL_TYPE_FK" FOREIGN KEY ("DEP_ID")
> REFERENCES "DEPOSITS" ("DEP_ID") ON DELETE CASCADE ENABLE);
>
> CREATE TABLE "LOCATIONS"
> ( "DEP_ID" NUMBER(12,0) NOT NULL ENABLE,
> "LINE" NUMBER(4,0) NOT NULL ENABLE,
> "COUNTRY" VARCHAR2(70 BYTE),
> "STATE_PROV" VARCHAR2(80 BYTE),
> "COUNTY" VARCHAR2(80 BYTE),
> CONSTRAINT "LOCATIONS_PK" PRIMARY KEY ("DEP_ID", "LINE") ENABLE,
> CONSTRAINT "LOCATIONS_FK" FOREIGN KEY ("DEP_ID")
> REFERENCES "USGS"."DEPOSITS_BASE" ("DEP_ID") ON DELETE CASCADE ENABLE);
>
> Actually, the above above are views, but for the purpose of simplicity, I've
> merged pertinent information and deleted extraneous 'stuff'.
>
> With the following data:
>
> Insert into DEPOSITS ("DEP_ID","NAME","DEV_ST","OPER_TP","SIG") values
> (55555555,'Delete This - Test Entry','Past Producer','Geothermal','N');
>
> Insert into COMMODITY ("DEP_ID","LINE","CODE","COMMOD","QUAL","IMPORT")
> values (55555555,5,'ABR','Abrasive',null,'Minor');
> Insert into COMMODITY ("DEP_ID","LINE","CODE","COMMOD","QUAL","IMPORT")
> values (55555555,4,'W_C','Tungsten','Mill Concentrate','Trace');
> Insert into COMMODITY ("DEP_ID","LINE","CODE","COMMOD","QUAL","IMPORT")
> values (55555555,3,'CU','Copper',null,'Minor');
> Insert into COMMODITY ("DEP_ID","LINE","CODE","COMMOD","QUAL","IMPORT")
> values (55555555,6,'AU','Gold',null,'Major');
> Insert into COMMODITY ("DEP_ID","LINE","CODE","COMMOD","QUAL","IMPORT")
> values (55555555,2,'AG','Silver',null,'Major');
>
> Insert into MODEL_TYPE ("DEP_ID","REC","USGS_NUM","MODEL_NAME") values
> (55555555,2,'29a','Quartz pebble conglomerate Au-U (BC name is Paleoplacer
> U-Au-PGE-Sn-Ti');
> Insert into MODEL_TYPE ("DEP_ID","REC","USGS_NUM","MODEL_NAME") values
> (55555555,1,'40a','Detachment-fault-related polymetallic Cu-Au-Ag-Pb-Zn
> deposits');
>
> Insert into LOCATIONS ("DEP_ID","LINE","COUNTRY","STATE_PROV","COUNTY")
> values (55555555,5,'United States','Colorado','Adams');
> Insert into LOCATIONS ("DEP_ID","LINE","COUNTRY","STATE_PROV","COUNTY")
> values (55555555,2,'United States','Montana','Deer Lodge');
> Insert into LOCATIONS ("DEP_ID","LINE","COUNTRY","STATE_PROV","COUNTY")
> values (55555555,3,'United States','Nevada','White Pine');
> Insert into LOCATIONS ("DEP_ID","LINE","COUNTRY","STATE_PROV","COUNTY")
> values (55555555,4,'United States','Nevada','Lander');
>
> I need to retrieve one line containing the data from DEPOSISTS, and whatever
> happens to be the first line (lowest rec or line) in the other tables. As it
> is, when I try to retrieve the joined data, I get 40 rows. I can retrieve
> just one row from the child tables with the "partition by", but I can't
> figure out how to tie them all together.
>
> Anybody know how to join the first row from all the tables together?
>
> Thanks,
> Bill Ferguson
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Feb 18 2007 - 18:52:57 CST