X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 084229FA60B for ; Thu, 22 Jun 2006 12:44:10 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 99106-04 for ; Thu, 22 Jun 2006 12:43:58 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.207]) by postgresql.org (Postfix) with ESMTP id 829379F9FE3 for ; Thu, 22 Jun 2006 12:43:57 -0300 (ADT) Received: by wx-out-0102.google.com with SMTP id s13so334636wxc for ; Thu, 22 Jun 2006 08:43:57 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=FqwqrqXQ09O5RpYad9BQbxF0b47UUFpq/r5LuO41dLhhqs00XZqvRzawrQn/v7XpPSAdidAq04E00b9XzwPIllUEVWHFQVduH5L3uRVgO+uIyxSnemSiYwtafN2Xl7165B6ZjkbbZ5xZen/i2/BssEegZK+S4qoHSeBfZjtIqEk= Received: by 10.70.27.7 with SMTP id a7mr3314016wxa; Thu, 22 Jun 2006 08:43:57 -0700 (PDT) Received: by 10.70.66.15 with HTTP; Thu, 22 Jun 2006 08:43:57 -0700 (PDT) Message-ID: Date: Thu, 22 Jun 2006 10:43:57 -0500 From: "Aaron Bono" To: "Sim Zacks" Subject: Re: join on next row Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9836_5407185.1150991037469" References: X-Google-Sender-Auth: b0fa15e377ee52d2 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/251 X-Sequence-Number: 25487 ------=_Part_9836_5407185.1150991037469 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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 ------=_Part_9836_5407185.1150991037469 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
------=_Part_9836_5407185.1150991037469--