Postgres Pro
Downloads
Home   >   mailing lists

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
"Justin Lintz"
Date:
what about putting a limit of 1 on your select statement, so you will just get the first event greater then the initial date for the employee?

On 6/18/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 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq



--
- Justin

Re: join on next row

From
"Aaron Bono"
Date:
I would use a stored procedure or function for this.  You order your results first by employee and then event date and finally even time.  Then you create a new result set from the first and return that.

That would probably be the most straight forward approach.

You could also try doing some thing like this (I have not tested it and so cannot vouch for its syntax but it should lead you close to another solution):

select
eventjoin.employee,
eventjoin.eventdate ,
eventjoin.eventtime,
eventjoin.eventtype,
eventjoin.maxeventtime,
e3.eventtype
from (
select
e1.employee,
e1.eventdate,
e1.eventtime,
e1.eventtype,
max(e2.eventtime) as maxeventtime
from events e1
inner join events e2 on (
e1.employee = e2.employee
and e1.eventDate = e2.eventDate
and e1.eventTime > e2.eventTime
)
order by
e1.employee
e1.eventDate
e1.eventTime
) eventjoin
inner join event e3 on (
e3.employee = eventjoin.employee
and e3.eventdate = eventjoin.eventdate
and e3.eventtime = eventjoin.maxeventtime
);

Who knows what the performance of this will be.  I would highly recommend you have employee in a separate table if you do not already.

-Aaron Bono

On 6/18/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