Re: tricky sql challenge
Date: 4 May 1994 18:12:04 -0700
Message-ID: <2q9h54$8mh_at_crl2.crl.com>
In article <CpA98F.Aon_at_vtm.be>, The Administrator <gerrit_at_vtm.be> wrote:
>In article <2q6bd4$djc_at_seismo.CSS.GOV> cooper_at_seismo.CSS.GOV (Dale Cooper) writes:
>>
>>OK, SQL wizards, a question to ponder:
>>
>>Can you come up with a single SQL update statement to solve the following
>>problem?
>>
>>We need to determine the maximum time window of a series of seismic events.
>>
>>In the case of a seismic event (earthquake, explosion), a single event occurs
>>at a given location at a certain time. However, the event may be recorded at
>>several monitoring stations around the world at different times. This, due to
>>a combination of geology and physics. What we need to do is create a single
>>record that shows the maximum time window of the event.
>>
>>A single event is characterized by a series of time values that overlap.
>>When there is no overlap, a new event is defined.
>>
>>Given the following table schema:
>>
>> eventid unique event identifier key
>> beg_time epoch time value of start of event
>> end_time epoch time value of end of event
>>
>
>What about
>UPDATE events A
>SET end_time = ( SELECT MAX(end_time)
> FROM events B
> WHERE B.begin_time between A.begin_time and A.end_time
> );
This would work only if run repeatedly until the iteration before the statement would produce "no records updated". It would work the first time in the simple sample, but if the data looked like:
0001 ==== 0002 ==== 0003 ===== 0004 ======= 0005 ==== A BC DE F G
It would take three iterations to extend 0005 to G.
And no, I haven't come up with a single sql statment solution, yet :-)
L. Scott
Research Planning, Inc.
(803) 256-7322
Received on Thu May 05 1994 - 03:12:04 CEST