Re: SQL question

Neil -

Relational tables normally store their rows without regard to order, as you probably know. Your data seems to be ordered, so here's what I think you need to do (I tried it and it works).

You need to add a dummy group column to your table so that SQL will know how you want the data grouped. The group number will change when your lith_code changes. Here is what you table will look like with the new column:

Group_no    HOLE    FROM    TO    LITH_CODE   COLOUR      ...
-------------     ----    ----    --    ---------   ------
1                DD1      0      2      Ts          rd          ..
1                DD1      2      4      Ts          gn
2                DD1      4      6      Um          bn
2                DD1      6      8      Um          gn
3                DD1      8     10      Upx         gn
4                DD1     10     12      Um          gy
5                DD2      0      2      Ts          Y
5                DD2      2      4      Ts          rd
6                DD2      4      6      Um          gy
6                DD2      6      8      Um          gn
7                DD2      8     10      Flt         gy
7                DD2     10     12      Flt         gn
8                DD2     12     14      Um          gy

Then, this SQL will give you the results you want:

select a.group_no, a.hole, min(a.from_val), max(a.to_val), a.lith_code from downholes a, downholes b
where a.group_no = b.group_no
group by a.group_no, a.hole, a.lith_code;

HTH,
Michael

Neil Moseley wrote:

> 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 - 12:49:30 CDT