join on next row

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

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

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

From each according to his ability. To each according to his ability to litigate.

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.

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

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:

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