Thread: join on next row

join on next row

From
Sim Zacks
Date:
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

Re: join on next row

From
"Gurjeet Singh"
Date:
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
>

Re: join on next row

From
Sim Zacks
Date:
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
>

Re: join on next row

From
"Gurjeet Singh"
Date:
    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
>

Re: join on next row

From
Martijn van Oosterhout
Date:
On Tue, Jun 20, 2006 at 05:13:50PM +0200, Sim Zacks wrote:
> Thank you for responding.
> I was thinking along those lines as well, though that would be an
> absolute performance killer.

I shouldn't be too bad, if you have the appropriate indexes defined.

However, it seems to me this is the kind of problem that is solved
trivially in a function. You simply store the previous row and when you
get the next one you output both.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment

Re: join on next row

From
Harald Fuchs
Date:
In article <e780u8$1h5e$1@news.hub.org>,
Sim Zacks <sim@compulab.co.il> writes:

> 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.

You can filter the others out by an OUTER JOIN:

  SELECT e1.Employee, e1.EventDate,
         e1.EventTime, e1.EventType,
         e2.EventTime, e2.EventType
  FROM events e1
  JOIN events e2 ON e2.Employee = e1.Employee
                AND e2.EventDate = e1.EventDate
                AND e2.EventTime > e1.EventTime
  LEFT JOIN events e3 ON e3.Employee = e1.Employee
                     AND e3.EventDate = e1.EventDate
                     AND e3.EventTime > e1.EventTime
                     AND e3.EventTime < e2.EventTime
  WHERE e3.EventID IS NULL
  ORDER BY e1.EventDate, e1.EventTime

Re: join on next row

From
Sim Zacks
Date:
Harold,
That's brilliant.
Sim

Harald Fuchs wrote:
> In article <e780u8$1h5e$1@news.hub.org>,
> Sim Zacks <sim@compulab.co.il> writes:
>
>> 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.
>
> You can filter the others out by an OUTER JOIN:
>
>   SELECT e1.Employee, e1.EventDate,
>          e1.EventTime, e1.EventType,
>          e2.EventTime, e2.EventType
>   FROM events e1
>   JOIN events e2 ON e2.Employee = e1.Employee
>                 AND e2.EventDate = e1.EventDate
>                 AND e2.EventTime > e1.EventTime
>   LEFT JOIN events e3 ON e3.Employee = e1.Employee
>                      AND e3.EventDate = e1.EventDate
>                      AND e3.EventTime > e1.EventTime
>                      AND e3.EventTime < e2.EventTime
>   WHERE e3.EventID IS NULL
>   ORDER BY e1.EventDate, e1.EventTime
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Re: join on next row

From
Aaron Evans
Date:
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.


Attachment

Re: join on next row

From
"Gurjeet Singh"
Date:
Thanks for pointing it out.... You are right; I forgot to add that...

On 6/20/06, Aaron Evans <aaron@aarone.org> wrote:
>
> 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:

Re: join on next row

From
"Merlin Moncure"
Date:
On 20 Jun 2006 18:20:55 +0200, Harald Fuchs <hf0406x@protecting.net> wrote:
> In article <e780u8$1h5e$1@news.hub.org>,
> Sim Zacks <sim@compulab.co.il> writes:
> > 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.
>
> You can filter the others out by an OUTER JOIN:
>
>   SELECT e1.Employee, e1.EventDate,
>          e1.EventTime, e1.EventType,
>          e2.EventTime, e2.EventType
>   FROM events e1
>   JOIN events e2 ON e2.Employee = e1.Employee
>                 AND e2.EventDate = e1.EventDate
>                 AND e2.EventTime > e1.EventTime
>   LEFT JOIN events e3 ON e3.Employee = e1.Employee
>                      AND e3.EventDate = e1.EventDate
>                      AND e3.EventTime > e1.EventTime
>                      AND e3.EventTime < e2.EventTime
>   WHERE e3.EventID IS NULL
>   ORDER BY e1.EventDate, e1.EventTime

This will only give the correct answer if the next event is on the
same day.  This does not match the problem as stated.  The actual
answer is more complex than it looks (in < pg 8.2).  In pg 8.2, you
can make:

SELECT e1.Employee, e1.EventDate,
       e1.EventTime, e1.EventType,
       e2.EventTime, e2.EventType
FROM events e1
JOIN events e2 ON
 (e2.Employee, e2.EventDate, e2.EventTime) >
 (e1.Employee, e1.EventDate, e1.EventTime)
LEFT JOIN events e3 ON
 (e3.Employee, e3.EventDate, e3.EventTime) >
 (e1.Employee, e1.EventDate, e1.EventTime) AND
 e3.EventTime < e2.EventTime
WHERE e3.EventID IS NULL
ORDER BY e1.EventDate, e1.EventTime

if you only want answers that match the same date as the selected
event, harald's answer is correct.  to get the correct answer in 8.1
and down you must make a monster of a sql statement ;)

merlin

Re: join on next row

From
Sim Zacks
Date:
Merlin,

Thank you for your input. My original question did specifically mention
that the events had to be on the same day.

> I need to have a query that gives per employee each event and the event after it if it happened _on the same day_.

Secondly, I hadn't seen that syntax in 8.2 yet. That is funky cool and I
will certainly be using it in the future.

Thanks
Sim

Merlin Moncure wrote:
> On 20 Jun 2006 18:20:55 +0200, Harald Fuchs <hf0406x@protecting.net> wrote:
>> In article <e780u8$1h5e$1@news.hub.org>,
>> Sim Zacks <sim@compulab.co.il> writes:
>> > 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.
>>
>> You can filter the others out by an OUTER JOIN:
>>
>>   SELECT e1.Employee, e1.EventDate,
>>          e1.EventTime, e1.EventType,
>>          e2.EventTime, e2.EventType
>>   FROM events e1
>>   JOIN events e2 ON e2.Employee = e1.Employee
>>                 AND e2.EventDate = e1.EventDate
>>                 AND e2.EventTime > e1.EventTime
>>   LEFT JOIN events e3 ON e3.Employee = e1.Employee
>>                      AND e3.EventDate = e1.EventDate
>>                      AND e3.EventTime > e1.EventTime
>>                      AND e3.EventTime < e2.EventTime
>>   WHERE e3.EventID IS NULL
>>   ORDER BY e1.EventDate, e1.EventTime
>
> This will only give the correct answer if the next event is on the
> same day.  This does not match the problem as stated.  The actual
> answer is more complex than it looks (in < pg 8.2).  In pg 8.2, you
> can make:
>
> SELECT e1.Employee, e1.EventDate,
>       e1.EventTime, e1.EventType,
>       e2.EventTime, e2.EventType
> FROM events e1
> JOIN events e2 ON
> (e2.Employee, e2.EventDate, e2.EventTime) >
> (e1.Employee, e1.EventDate, e1.EventTime)
> LEFT JOIN events e3 ON
> (e3.Employee, e3.EventDate, e3.EventTime) >
> (e1.Employee, e1.EventDate, e1.EventTime) AND
> e3.EventTime < e2.EventTime
> WHERE e3.EventID IS NULL
> ORDER BY e1.EventDate, e1.EventTime
>
> if you only want answers that match the same date as the selected
> event, harald's answer is correct.  to get the correct answer in 8.1
> and down you must make a monster of a sql statement ;)
>
> merlin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>

Re: join on next row

From
"Merlin Moncure"
Date:
On 6/25/06, Sim Zacks <sim@compulab.co.il> wrote:
> Merlin,
>
> Thank you for your input. My original question did specifically mention
> that the events had to be on the same day.
>
> > I need to have a query that gives per employee each event and the event after it if it happened _on the same day_.


whoop! :) oh well. heh