Re: join on next row

    I agree about the performance; but it won't be that bad if PG can
unnest these subqueries and convert them into join views!!! In that
case, these views would return just one row (LIMIT 1), and that is the
best a developer can do to help the optimizer make the decision. If
the optimizer knows that a relation in the join is going to return
just one row, it would try to evaluate that relation first, yeilding
better performance in the subsequent join operations.

    But I dont think we have a choice; unless, of course, if someday
PG starts supporting Oracle-like 'lateral views', where we can write
have predicates in the where clause of a view which refer the columns
of another relation in the join which this view is a part of!!!

    Do let us all know if you find a better solution.

Regards,
Gurjeet.

On 6/20/06, Sim Zacks <sim@compulab.co.il> wrote:
> Thank you for responding.
> I was thinking along those lines as well, though that would be an
> absolute performance killer.
>
> 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.
> >
> > On 6/20/06, Sim Zacks <sim@compulab.co.il> wrote:
> >> I am having brain freeze right now and was hoping someone could help me
> >> with a (fairly) simple query.
> >>
> >> I need to join on the next row in a similar table with specific criteria.
> >>
> >> I have a table with events per employee.
> >> I need to have a query that gives per employee each event and the event
> >> after it if it happened on the same day.
> >>
> >> The Events table structure is:
> >>
> >> EventID
> >> Employee
> >> EventDate
> >> EventTime
> >> EventType
> >>
> >> 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.
> >>
> >> Thank You
> >> Sim
> >>
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 1: if posting/reading through Usenet, please send an appropriate
> >>        subscribe-nomail command to majordomo@postgresql.org so that your
> >>        message can get through to the mailing list cleanly
> >>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> >               http://www.postgresql.org/docs/faq
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>