From: Sim Zacks <sim@compulab.co.il>
Subject: join on next row
Date: Sun, 18 Jun 2006 13:14:33 +0200
Organization: Hub.Org Networking Services
Lines: 44
Message-ID: <e7396t$18o8$1@news.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Thunderbird 1.5.0.4 (Windows/20060516)
To: pgsql-sql@postgresql.org

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