From: | Aaron Evans <aaron(at)aarone(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: join on next row |
Date: | 2006-06-20 16:46:25 |
Message-ID: | 6624C7D0-5690-4586-8DD4-8DCE47BC8D6E@aarone.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Worky Workerson | 2006-06-20 16:47:50 | Exporting data from view |
Previous Message | Harald Fuchs | 2006-06-20 16:20:55 | Re: join on next row |