Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search for
  Advanced Search

join on next row


  • From: Sim Zacks <sim(at)compulab(dot)co(dot)il>
  • To: pgsql-general(at)postgresql(dot)org
  • Subject: join on next row
  • Date: Tue, 20 Jun 2006 08:24:01 +0200
  • Message-id: <e780u8$1h5e$1(at)news(dot)hub(dot)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



Home | Main Index | Thread Index

Privacy Policy | PostgreSQL Archives hosted by Command Prompt, Inc. | Designed by tinysofa
Copyright © 1996 – 2008 PostgreSQL Global Development Group