Re: join on next row

On 20 Jun 2006 18:20:55 +0200, Harald Fuchs <hf0406x(at)protecting(dot)net> wrote:
> In article <e780u8$1h5e$1(at)news(dot)hub(dot)org>,
> Sim Zacks <sim(at)compulab(dot)co(dot)il> writes:
> > To get this result set it would have to be an inner join on employee
> > and date where the second event time is greater then the first. But I
> > don't want the all of the records with a greater time, just the first
> > event after.
>
> You can filter the others out by an OUTER JOIN:
>
> SELECT e1.Employee, e1.EventDate,
> e1.EventTime, e1.EventType,
> e2.EventTime, e2.EventType
> FROM events e1
> JOIN events e2 ON e2.Employee = e1.Employee
> AND e2.EventDate = e1.EventDate
> AND e2.EventTime > e1.EventTime
> LEFT JOIN events e3 ON e3.Employee = e1.Employee
> AND e3.EventDate = e1.EventDate
> AND e3.EventTime > e1.EventTime
> AND e3.EventTime < e2.EventTime
> WHERE e3.EventID IS NULL
> ORDER BY e1.EventDate, e1.EventTime

This will only give the correct answer if the next event is on the
same day. This does not match the problem as stated. The actual
answer is more complex than it looks (in < pg 8.2). In pg 8.2, you
can make:

SELECT e1.Employee, e1.EventDate,
e1.EventTime, e1.EventType,
e2.EventTime, e2.EventType
FROM events e1
JOIN events e2 ON
(e2.Employee, e2.EventDate, e2.EventTime) >
(e1.Employee, e1.EventDate, e1.EventTime)
LEFT JOIN events e3 ON
(e3.Employee, e3.EventDate, e3.EventTime) >
(e1.Employee, e1.EventDate, e1.EventTime) AND
e3.EventTime < e2.EventTime
WHERE e3.EventID IS NULL
ORDER BY e1.EventDate, e1.EventTime

if you only want answers that match the same date as the selected
event, harald's answer is correct. to get the correct answer in 8.1
and down you must make a monster of a sql statement ;)

merlin