Re: "next"

On Mon, Dec 02, 2002 at 04:54:43PM +0000, Malcolm Hutty wrote:
>
> Suppose I've got a table containing train departure and arrival times,
> like so:
>
> SELECT id,departure,arrival,arrival-departure AS duration FROM timetable;
>
> id | departure | arrival | duration
> ----+------------------------+------------------------+----------
> 1 | 2002-12-02 14:00:00+00 | 2002-12-02 15:00:00+00 | 01:00
> 7 | 2002-12-02 17:00:00+00 | 2002-12-03 14:00:00+00 | 21:00
> 2 | 2002-12-03 17:00:00+00 | 2002-12-03 18:00:00+00 | 01:00
> 6 | 2002-12-03 21:00:00+00 | 2002-12-04 04:00:00+00 | 07:00
> 4 | 2002-12-04 10:00:00+00 | 2002-12-04 18:00:00+00 | 08:00
> 5 | 2002-12-05 08:00:00+00 | 2002-12-05 10:00:00+00 | 02:00
>
>
> Can anyone advise as to the best way to express the following question
> in SQL: "What's the earliest arrival time where there isn't a
> subsequent departure time within (e.g.) 7 hours?". The table will
> contain many rows.
>
> I feel foolish - it _looks_ simple - but I've been banging my head
> against the wall trying to implement this as either a JOIN or a
> subselect, and it's starting to hurt.

create table trains (id serial primary key, depart timestamp not null,
arrive timestamp not null check (arrive > depart));

insert into train (depart,arrive) values ('2002-01-01 1:00
PM','2002-01-01 2:00 PM');

insert into trains (depart,arrive) values ('2002-01-01 1:00
PM','2002-01-01 2:00 PM');

insert into trains (depart,arrive) values ('2002-01-01 4:00
PM','2002-01-01 5:00 PM');

insert into trains (depart,arrive) values ('2002-01-02 4:00
PM','2002-01-01 5:00 PM');

insert into trains (depart,arrive) values ('2002-01-02 4:00
PM','2002-02-01 5:00 PM');

insert into trains (depart,arrive) values ('2002-01-09 4:00
PM','2002-02-09 5:00 PM');

joel(at)joel=# select * from trains;
id | depart | arrive
----+---------------------+---------------------
1 | 2002-01-01 13:00:00 | 2002-01-01 14:00:00
2 | 2002-01-01 16:00:00 | 2002-01-01 17:00:00
4 | 2002-01-02 16:00:00 | 2002-02-01 17:00:00
5 | 2002-01-09 16:00:00 | 2002-02-09 17:00:00

Ok, so trains 2 and 4 have arrivals where the last departure is more
than 7 hours away. And train 5 will also appear, since there is no
departure after it.

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 );

to find these earliest of these, just order them by arrive, and limit 1
on the outer query, as such:

select id,arrive from trains t1 where t1.arrive + '7 hours' < all
(select depart from trains t2 where t2.depart > t1.arrive ) order by
arrive limit 1;

And we get train #1, which is the earliest train arrival that meets the
requirements.

--

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant