X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 91E699FA432 for ; Tue, 12 Sep 2006 15:16:18 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 74556-06 for ; Tue, 12 Sep 2006 15:14:59 -0300 (ADT) X-Greylist: delayed 00:05:01.554721 by SQLgrey- Received: from sccrmhc12.comcast.net (sccrmhc12.comcast.net [204.127.200.82]) by postgresql.org (Postfix) with ESMTP id BCAED9FA2D5 for ; Tue, 12 Sep 2006 15:14:58 -0300 (ADT) Received: from [10.100.103.134] (unknown[38.117.204.66]) by comcast.net (sccrmhc12) with SMTP id <20060912180955012005f2ive>; Tue, 12 Sep 2006 18:09:55 +0000 User-Agent: Microsoft-Entourage/11.2.5.060620 Date: Tue, 12 Sep 2006 14:09:53 -0400 Subject: Re: Joint a table back on itself? From: Daryl Richter To: , Message-ID: Thread-Topic: [SQL] Joint a table back on itself? Thread-Index: AcbWlqUZ46eKO0KJEduFIwARJOT9EA== In-Reply-To: <20060912160100.D624D9FA374@postgresql.org> Mime-version: 1.0 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200609/80 X-Sequence-Number: 26291 On 9/12/06 11:55 AM, "paallen@attglobal.net" wrote: > Hi all, >=20 > I have a union query that generates a table with > directional measurments (a=3Dazimuth, i=3Ddepth) at > various depths (md) down a hole. The results look > like: > hole_id | md | a | i | e > | n | v > -----------+--------+--------+--------+------------------+---------------= ---+- > ----------------- > GND-06-65 | 0 | 90 | -75 | > 795187.927 | 9228405.685 | 3945.199 > GND-06-65 | 19 | 90.37 | -74.42 | > 795192.937315893 | 9228405.66852282 | 3926.87160812059 > GND-06-65 | 28 | 91.18 | -74.49 | > 795195.348994385 | 9228405.63593718 | 3918.20081588081 > GND-06-65 | 37 | 91.04 | -74.53 | > 795197.752173187 | 9228405.5893705 | 3909.52772202531 > GND-06-65 | 46 | 92.38 | -74.56 | > 795200.149282893 | 9228405.51783377 | 3900.85313364721 > GND-06-65 | 55 | 92.86 | -74.55 | > 795202.543576384 | 9228405.40826886 | 3892.17815120329 >=20 > The depths ( md column) will always start with > zero and the intervals will be variable. >=20 > So how can I join this view back onto itself so > each record is joined to the next record? Such as: >=20 > md1 | a1 | i1 | e1 | > n1 | v1 | md2 | a2 > | i2 | e2 | n2 | > v2 | >=20 > --------+--------+--------+------------------+------------------+--------= ----- > -----|--------+--------+--------+------------------+------------------+--= ----- > ----------- > 0 | 90 | -75 | 795187.927 | > 9228405.685 | 3945.199 | 19 | 90.37 > | -74.42 | 795192.937315893 | 9228405.66852282 | > 3926.87160812059 > 19 | 90.37 | -74.42 | 795192.937315893 | > 9228405.66852282 | 3926.87160812059 | 28 | > 91.18 | -74.49 | 795195.348994385 | > 9228405.63593718 | 3918.20081588081 > 28 | 91.18 | -74.49 | 795195.348994385 | > 9228405.63593718 | 3918.20081588081 | 37 | > 91.04 | -74.53 | 795197.752173187 | > 9228405.5893705 | 3909.52772202531 > 37 | 91.04 | -74.53 | 795197.752173187 | > 9228405.5893705 | 3909.52772202531 | 46 | > 92.38 | -74.56 | 795200.149282893 | > 9228405.51783377 | 3900.85313364721 > 46 | 92.38 | -74.56 | 795200.149282893 | > 9228405.51783377 | 3900.85313364721 | 55 | > 92.86 | -74.55 | 795202.543576384 | > 9228405.40826886 | 3892.17815120329 >=20 > My reason for wanting this is so I can joint this > table with a between clause to another table with > depth measurments recorded along this hole and > perform a calculation. Leaving out the extra columns: create table holes( hole_id text, md int, a decimal ); insert into holes( hole_id, md, a ) values ( 'GND-06-65', 0, 90 ); insert into holes( hole_id, md, a ) values ( 'GND-06-65', 19, 90.37 ); insert into holes( hole_id, md, a ) values ( 'GND-06-65', 28, 91.18 ); insert into holes( hole_id, md, a ) values ( 'GND-06-65', 37, 91.04 ); insert into holes( hole_id, md, a ) values ( 'GND-06-65', 46, 92.38 ); insert into holes( hole_id, md, a ) values ( 'GND-06-65', 55, 92.86 ); go select a.md as md1, a.a as a1, b.md as md2, b.a as a2 from holes a join holes b on b.hole_id =3D a.hole_id where b.md =3D ( select min( c.md ) from holes c where c.hole_id =3D a.hole_id and c.a > a.a ) order by a.md, b.md; md1 a1 md2 a2 ------ ----- ------ ----- 0 90 19 90.37 19 90.37 28 91.18 28 91.18 46 92.38 37 91.04 28 91.18 46 92.38 55 92.86 5 record(s) selected [Fetch MetaData: 8/ms] [Fetch Data: 0/ms] [Executed: 9/12/06 2:05:52 PM EDT ] [Execution: 123/ms] >=20 > Thanks, >=20 > Phillip J. Allen > Consulting Geochemist > paallen@attglobal.net >=20 >=20 >=20 >=20 > The union query is as follows: > SELECT c.hole_id, 0 AS md, c.collar_azimuth AS a, > c.collar_dip AS i, c.e_utm AS e, c.n_utm AS n, > c.elv_utm AS v > FROM dh_collar AS c > WHERE (((c.hole_id)=3D'GND-06-65')) >=20 > UNION >=20 > SELECT s.hole_id, s.depth_m AS md, s.azimuth AS a, > s.vert_dip AS i, s.XE AS e, s.XN AS n, s.XR AS v > FROM dh_survey AS s > WHERE (((s.hole_id)=3D'GND-06-65')) >=20 > UNION SELECT s.hole_id, c.depth_m AS md, s.azimuth > AS a, s.vert_dip AS i, s.XE AS e, s.XN AS n, s.XR AS v > FROM dh_collar AS c RIGHT JOIN dh_survey AS s ON > c.hole_id =3D s.hole_id > WHERE (((s.depth_m)=3D > ( > SELECT Max(stmp.depth_m) AS MaxOfdepth_m > FROM dh_survey AS stmp > GROUP BY stmp.hole_id > HAVING (((stmp.hole_id)=3D'GND-06-65')) > )) AND ((s.hole_id)=3D'GND-06-65')); >=20 >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? >=20 > http://www.postgresql.org/docs/faq -- Daryl Email *my =3D [ daryl at: @"eddl" dot: @"us" ]; Weblog *blog =3D @=B2http://itsallsemantics.com=B2;