On 9/12/06 11:55 AM, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> Hi all, > > I have a union query that generates a table with > directional measurments (a=azimuth, i=depth) 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 > > The depths ( md column) will always start with > zero and the intervals will be variable. > > So how can I join this view back onto itself so > each record is joined to the next record? Such as: > > md1 | a1 | i1 | e1 | > n1 | v1 | md2 | a2 > | i2 | e2 | n2 | > v2 | > > --------+--------+--------+------------------+------------------+------------- > -----|--------+--------+--------+------------------+------------------+------- > ----------- > 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 > > 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 = a.hole_id where b.md = ( select min( c.md ) from holes c where c.hole_id = 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] > > Thanks, > > Phillip J. Allen > Consulting Geochemist > [EMAIL PROTECTED] > > > > > 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)='GND-06-65')) > > UNION > > 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)='GND-06-65')) > > 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 = s.hole_id > WHERE (((s.depth_m)= > ( > SELECT Max(stmp.depth_m) AS MaxOfdepth_m > FROM dh_survey AS stmp > GROUP BY stmp.hole_id > HAVING (((stmp.hole_id)='GND-06-65')) > )) AND ((s.hole_id)='GND-06-65')); > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Daryl Email *my = [ daryl at: @"eddl" dot: @"us" ]; Weblog *blog = @²http://itsallsemantics.com²; ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
