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

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
>