Re: join on next row

Merlin,

Thank you for your input. My original question did specifically mention
that the events had to be on the same day.

> I need to have a query that gives per employee each event and the event after it if it happened _on the same day_.

Secondly, I hadn't seen that syntax in 8.2 yet. That is funky cool and I
will certainly be using it in the future.

Thanks
Sim

Merlin Moncure wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>