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.