Sorry it took so long to to SUM this.
I received several interesting solutions suggesting several different
methods. These included a concise SQL query with subselects that would work
if MI had a full SQL implementation, and several methods for coding each
group of records (formations in a boring) in sequence (using rowid), then
querying within the sequence. T. Warman suggested adding an explicit field
to the table called "Overlies". S. Cox also offered an awk script that is
delightfully brief (ah, I remember those awk days fondly), but I never got
around to getting an NT awk-ifier to run it with. Probably because the work
would have to be done outside of MI.
My solution is relatively painless and evolved mainly from a solution from
B. O'Donovan:
(1) Select top of bedrock:
select boring, formation, min(top)
from logdata
where formation = "R"
into toporock
Note that the bottom of the surficial units (where not formcode =
"R") cannot be identified by a similar query. Max() selects the record
following (lower) deepest surficial formation, whereas min() correctly
selects the top of bedrock, as shown.
Also note that the select statement is considered dangerous to the
SQL cognoscienti because the non-aggregate values don't necessarily relate
uniquely to the aggregate value. Indeed, SQL implementations "more
sophisticated" than MI's won't allow this sort of statement. To be
rigorous, the non-agg columns should be included in the group by statement
[P. Strain]
(2) Select first unit above bedrock by matching respective "top" and
"bottom" of units:
Select formtable.boring, formtable.formation, formtable.top,
formtable.bottom
from formtable, toporock
where formtable.boring=toporock.boring and formatable.bottom = toporock.top
As Brendan noted, there may be a more elegant solution, but I haven't found
it yet. Attempts to use subselects always failed. In addition to those
mentioned above, thanks go to C. Vavilala, C. Huyck, and M. Higham. I need
not add, but will anyway, that this list has once again proved itself
invaluable.
Happily initiating the 3D phase,
Drew
-----Original Message-----
From: Phillips, Andrew [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 10, 2000 2:07 PM
To: mi-l (E-mail)
Subject: MI SQL and aggregate functions
I have a table of borings with formation data. The structure is something
like this:
Boring Formation Top Bottom
A S 0 2
A F 2 22
A D 22 32
A R 32 150
B F 0 25
B . . .
. . . .
. . . .
I would like to select out the record that lies above each "R" formation
(i.e., the first formation above bedrock in this case). The most
successful SQL statement that I have tried is:
Select Boring, Formation, Max(Top)
>From Formtable
where Formation <>"R" group by Boring order by Boring
into firstup
Although this does return the correct depth of the "first formation", values
in the other fields are from the previous (higher) record. How can I select
the correct record?
Thanks, Drew
________________________
Andrew Phillips, Ph.D.
Quaternary Geology Section
Illinois State Geological Survey
________________________
----------------------------------------------------------------------
To unsubscribe from this list, send e-mail to [EMAIL PROTECTED] and put
"unsubscribe MAPINFO-L" in the message body, or contact [EMAIL PROTECTED]
----------------------------------------------------------------------
To unsubscribe from this list, send e-mail to [EMAIL PROTECTED] and put
"unsubscribe MAPINFO-L" in the message body, or contact [EMAIL PROTECTED]
