Re: join on next row

Harold,
That's brilliant.
Sim

Harald Fuchs wrote:
> In article <e780u8$1h5e$1@news.hub.org>,
> Sim Zacks <sim@compulab.co.il> writes:
>
>> I want my query resultset to be
>> Employee,EventDate(1),EventTime(1),EventType(1),EventTime(2),EventType(2)
>> Where Event(2) is the first event of the employee that took place
>> after the other event.
>
>> Example
>> EventID    Employee    EventDate    EventTime    EventType
>> 1    John        6/15/2006    7:00        A
>> 2    Frank        6/15/2006    7:15        B
>> 3    Frank        6/15/2006    7:17        C
>> 4    John        6/15/2006    7:20        C
>> 5    Frank        6/15/2006    7:25        D
>> 6    John        6/16/2006    7:00        A
>> 7    John        6/16/2006    8:30        R
>
>> Expected Results
>> John, 6/15/2006, 7:00, A, 7:20, C
>> Frank, 6/15/2006, 7:15, B, 7:17, C
>> Frank, 6/15/2006, 7:17, C, 7:25, D
>> John, 6/16/2006, 7:00, A, 8:30, R
>
>> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>