Re: tricky sql challenge

>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.
>
>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 we would like to have is a table that reflects the minimum start time
>and the maximum end time. The row that would be updated would be the row
>with the smallest start time of the group.
>
>If anyone can come up a nifty solution to this, I would be much obliged.
>

try:

update event a set end_time =
(select max(end)
from event
connect by prior end_time between beg_time and end and eventid != prior eventid
start with a.eventid)
/

L. Scott Johnson
Senior Programmer
Research Planning, Inc.
1200 Park Street
Columbia, SC
(803) 256-7322 Received on Thu May 05 1994 - 21:57:17 CEST