SQL question

G'day

I want to do a form of data extraction on drill hole data.

A typical downhole table might look like this:

HOLE FROM TO LITH_CODE COLOUR ... ---- ---- -- --------- ------

DD1      0      2      Ts          rd          ..
DD1      2      4      Ts          gn
DD1      4      6      Um          bn
DD1      6      8      Um          gn
DD1      8     10      Upx         gn
DD1     10     12      Um          gy
DD2      0      2      Ts          Y
DD2      2      4      Ts          rd
DD2      4      6      Um          gy
DD2      6      8      Um          gn
DD2      8     10      Flt         gy
DD2     10     12      Flt         gn
DD2     12     14      Um          gy

with Lith_Codes commonly repeated for adjoining intervals because other information is also recorded.

What I would like to return is:

HOLE    FROM   TO     LITH_CODE
----    ----   --     ---------
DD1      0      4      Ts
DD1      4      8      Um
DD1      8     10      Upx
DD1     10     12      Um
DD2      0      4      Ts
DD2      4      8      Um
DD2      8     12      Flt
DD2     12     14      Um

Unfortunately, Group by with Max & Min on the 'From' and 'To' only works where the identical Lith_Codes are consecutive and do not occur again later in the table.

Any hints would be appreciated.

Regards

Neil Moseley
mose_at_iinet.net.au Received on Fri May 14 1999 - 09:55:25 CDT