Re: tricky sql challenge
Date: Wed, 4 May 1994 14:50:39 GMT
Message-ID: <CpA98F.Aon_at_vtm.be>
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
>
> (Stuff deleted)
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 );
?????
Gerrit.
--- Gerrit Cap Vlaamse Televisie Maatschappij N.V. e-mail : gerrit_at_vtm.be Medialaan 1 fax : +32 2 253.12.21 B-1800 Vilvoorde Belgium voice : +32 2 255.38.72 -- Gerrit Cap Vlaamse Televisie Maatschappij N.V. e-mail : gerrit_at_vtm.be Medialaan 1 fax : +32 2 253.12.21 B-1800 Vilvoorde Belgium voice : +32 2 255.38.72Received on Wed May 04 1994 - 16:50:39 CEST