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 ACB809FA430 for ; Thu, 22 Jun 2006 03:25:17 -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 24930-05 for ; Thu, 22 Jun 2006 03:25:11 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.182]) by postgresql.org (Postfix) with ESMTP id EF9A99F9ED6 for ; Thu, 22 Jun 2006 03:25:09 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id f25so217231pyf for ; Wed, 21 Jun 2006 23:25:08 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=oIIKpSFEcGb5QHWl4hmy9EV4V1+ZBWZaEKjjNi6qswd24Lt3Mke5dUpAaiE460sxHd5XQMODkeWOcjmaS1CsuOW8Bm8cBrCCsF9PbNRsHzYZpAmb5wrJU7RTqEWVBPSdEREuIeoHoMfErAiZr+EgfKZR32/hCQaCswEBZzef4gM= Received: by 10.35.99.14 with SMTP id b14mr713749pym; Wed, 21 Jun 2006 23:25:07 -0700 (PDT) Received: by 10.35.100.13 with HTTP; Wed, 21 Jun 2006 23:25:07 -0700 (PDT) Message-ID: <99edfc5e0606212325n1a564767o3ff43c92a6009a49@mail.gmail.com> Date: Thu, 22 Jun 2006 02:25:07 -0400 From: "Justin Lintz" 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_2962_2802498.1150957507868" References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/247 X-Sequence-Number: 25483 ------=_Part_2962_2802498.1150957507868 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline what about putting a limit of 1 on your select statement, so you will just get the first event greater then the initial date for the employee? 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- - Justin ------=_Part_2962_2802498.1150957507868 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline what about putting a limit of 1 on your select statement, so you will just get the first event greater then the initial date for the employee?

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq



--
- Justin ------=_Part_2962_2802498.1150957507868--