From: | Harald Fuchs <hf0406x(at)protecting(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: join on next row |
Date: | 2006-06-20 16:20:55 |
Message-ID: | puhd2flrx4.fsf@srv.protecting.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
In article <e780u8$1h5e$1(at)news(dot)hub(dot)org>,
Sim Zacks <sim(at)compulab(dot)co(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Aaron Evans | 2006-06-20 16:46:25 | Re: join on next row |
Previous Message | Lee Riquelmei | 2006-06-20 15:39:09 | Re: question about performance of libpq |