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@protecting.net> wrote:
>> In article <e780u8$1h5e$1@news.hub.org>,
>> Sim Zacks <sim@compulab.co.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
>