[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] join on next row
From: "Aaron Bono" <postgresql () aranya ! com>
Date: 2006-06-22 15:43:57
Message-ID: bf05e51c0606220843h2211534exa5d0cd3023565e81 () mail ! gmail ! com
[Download RAW message or body]
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
[Attachment #3 (text/html)]
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.<br><br>That would probably be the most \
straight forward approach. <br><br>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):<br><br>select<br>eventjoin.employee,<br>eventjoin.eventdate \
,<br>eventjoin.eventtime,<br>eventjoin.eventtype,<br>eventjoin.maxeventtime,<br>e3.eventtype<br>from \
(<br>select<br>e1.employee,<br>e1.eventdate,<br>e1.eventtime,<br>e1.eventtype,<br>max(e2.eventtime) \
as maxeventtime<br>from events e1 <br>inner join events e2 on (<br>e1.employee = \
e2.employee<br>and e1.eventDate = e2.eventDate<br>and e1.eventTime > \
e2.eventTime<br>)<br>order by <br>e1.employee<br>e1.eventDate<br>e1.eventTime<br>) \
eventjoin<br>inner join event e3 on ( <br>e3.employee = eventjoin.employee<br>and \
e3.eventdate = eventjoin.eventdate<br>and e3.eventtime = \
eventjoin.maxeventtime<br>);<br><br>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. <br><br>-Aaron Bono<br><br><div><span class="gmail_quote">On 6/18/06, <b \
class="gmail_sendername">Sim Zacks</b> <<a \
href="mailto:sim@compulab.co.il">sim@compulab.co.il</a>> wrote:</span><blockquote \
class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt \
0pt 0.8ex; padding-left: 1ex;"> I am having brain freeze right now and was hoping \
someone could help me<br>with a (fairly) simple query.<br><br>I need to join on the \
next row in a similar table with specific criteria.<br><br>I have a table with events \
per employee. <br>I need to have a query that gives per employee each event and the \
event<br>after it if it happened on the same day.<br><br>The Events table structure \
is:<br><br>EventID<br>Employee<br>EventDate<br>EventTime<br>EventType <br><br>I want \
my query resultset to \
be<br>Employee,EventDate(1),EventTime(1),EventType(1),EventTime(2),EventType(2)<br>Where \
Event(2) is the first event of the employee that took place after<br>the other \
event.<br><br>Example <br>EventID \
Employee EventDate \
EventTime \
EventType<br>1 \
John 6/15/2006 \
7:00 A<br>2 \
Frank \
6/15/2006 \
7:15 B<br>3 \
Frank \
6/15/2006 \
7:17 C \
<br>4 \
John 6/15/2006 \
7:20 C<br>5 \
Frank \
6/15/2006 \
7:25 D<br>6 \
John 6/16/2006 \
7:00 A<br>7 \
John 6/16/2006 \
8:30 R \
<br><br>Expected Results<br>John, 6/15/2006, 7:00, A, 7:20, C<br>Frank, 6/15/2006, \
7:15, B, 7:17, C<br>Frank, 6/15/2006, 7:17, C, 7:25, D<br>John, 6/16/2006, 7:00, A, \
8:30, R<br><br>To get this result set it would have to be an inner join on employee \
and <br>date where the second event time is greater then the first. But I \
don't<br>want the all of the records with a greater time, just the first event \
after.<br><br>Thank You<br>Sim</blockquote></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic