Re: Stuck on analytics
- From: Anthony Wilson <amwilson@xxxxxxxxxxxx>
- To: wbfergus@xxxxxxxxx
- Date: Mon, 19 Feb 2007 09:52:57 +0900
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@xxxxxxxxxxxxxxxxxxxxx()> /
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@xxxxxxxxx>:
> 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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
