pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedJoint a table back on itself?
2+ messages / 2 participants
[nested] [flat]
* Joint a table back on itself?
@ 2006-09-12 15:55 paallen@attglobal.net
2006-09-12 18:09 ` Re: Joint a table back on itself? Daryl Richter <daryl@eddl.us>
0 siblings, 1 reply; 2+ messages in thread
From: paallen@attglobal.net @ 2006-09-12 15:55 UTC (permalink / raw)
To: 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.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.
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'));
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Joint a table back on itself?
2006-09-12 15:55 Joint a table back on itself? paallen@attglobal.net
@ 2006-09-12 18:09 ` Daryl Richter <daryl@eddl.us>
0 siblings, 0 replies; 2+ messages in thread
From: Daryl Richter @ 2006-09-12 18:09 UTC (permalink / raw)
To: paallen@attglobal.net; pgsql-sql
On 9/12/06 11:55 AM, "paallen@attglobal.net" <paallen@attglobal.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@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'));
>
>
> ---------------------------(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²;
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-09-12 18:09 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-12 15:55 Joint a table back on itself? paallen@attglobal.net
2006-09-12 18:09 ` Daryl Richter <daryl@eddl.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox