Re: tricky sql challenge

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.72