Re: Joint a table back on itself?

On 9/12/06 11:55 AM, "paallen(at)attglobal(dot)net" <paallen(at)attglobal(dot)net> 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
> paallen(at)attglobal(dot)net
>
>
>
>
> 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²;