Re: "next"

On Mon, Dec 02, 2002 at 06:10:39PM +0000, Malcolm Hutty wrote:
> Joel Burton wrote:
>
> >We can find these with:
> >
> >select id,
> >       arrive
> >from   trains t1
> >where  t1.arrive + '7 hours' < all ( select depart
> >                                     from   trains t2
> >                     where  t2.depart > t1.arrive );
>
> Thanks, that really helped. It was the "all" that did it; I'd been
> messing with IN and EXISTS and generally making a mess of it.

Glad to help.

Re: EXISTS, I think that this would be equivalent:

select id,
       arrive
from   trains t1
where  not exists ( select *
                    from   trains t2
            where  t2.depart > t1.arrive
            and    t2.depart - t1.arrive <= '7 hours' )

the t2.depart > t1.arrive is to get rid of most matches, rather than
relying on the mucher slower subtraction.

This might perform faster or slower than the the < ALL, depending on
your data, indexes, etc. I'd think it would be slower, but benchmark if
it's important. I think the first is definitely clearer, though.

BTW, for SQL novices, there's also ANY, similar to ALL, which finds
cases where there's any match. This can be easily switched for EXISTS.
If you'd like some help using these, I'd highly recommend Joe Celko's
_SQL_For_Smarties_.

--

Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant