Re: join on next row - Mailing list pgsql-general

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
>