Re: join on next row

sorry to nitpick, but I think that to get this query to do exactly
what you want you'll need to add ordering over EventTime on your sub-
selects to assure that you get the next event and not just some event
later event on the given day.

-ae

On Jun 20, 2006, at 11:12 AM, Gurjeet Singh wrote:

> Gurjeet Singh wrote:
> > It would have been quite easy if done in Oracle's 'lateral view'
> > feature. But I think it is achievable in standard SQL too; using
> > subqueries in the select-clause.
> >
> > Try something like this:
> >
> > select
> >     Employee, EventDate,
> >     EventTime as e1_time,
> >     EventType as e1_type,
> >     (    select
> >             EventTime
> >         from
> >             Events
> >         where    Employee = O.Employee
> >         and        EventDate = O.EventDate
> >         and        EventTime > O.EventTime
> >         limit    1
> >     )as e_time_1,
> >     (    select
> >             EventType
> >         from
> >             Events
> >         where    Employee = O.Employee
> >         and        EventDate = O.EventDate
> >         and        EventTime > O.EventTime
> >         limit    1
> >     )
> > from
> >     Events
> >
> > Hope it helps...
> >
> > Regards,
> > Gurjeet.