Joint a table back on itself? - Mailing list pgsql-sql

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.199GND-06-65 |     19 |  90.37 | -74.42 |
795192.937315893 | 9228405.66852282 | 3926.87160812059GND-06-65 |     28 |  91.18 | -74.49 |
795195.348994385 | 9228405.63593718 | 3918.20081588081GND-06-65 |     37 |  91.04 | -74.53 |
795197.752173187 |  9228405.5893705 | 3909.52772202531GND-06-65 |     46 |  92.38 | -74.56 |
795200.149282893 | 9228405.51783377 | 3900.85313364721GND-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.

Thanks,

Phillip J. Allen
Consulting Geochemist
paallen@attglobal.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'));