Re: tricky sql challenge

In article <2q6bd4$djc_at_seismo.CSS.GOV>, Dale Cooper <cooper_at_seismo.CSS.GOV> wrote:

>
>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.
>
>
 ...
>Thanks,
>
>Dale Cooper
>Center for Seismic Studies
>Arlington, VA

This does not look pretty, but I think should do the job:

select a.eventid, a.beg_time, b.end_time from test_table a, test_table b
where not exists (select 'x' from test_table c

                  where c.end_time>=a.beg_time and c.beg_time<a.beg_time)
and b.end_time=(select min(end_time) from test_table d
                where a.beg_time<= d.end_time
                and not exists (select 'x' from test_table e
                  where e.beg_time<=d.end_time and e.end_time>d.end_time));
                          

Mirek Piaseczny Received on Thu May 05 1994 - 22:44:42 CEST