From: Justin Lintz <jlintz@gmail.com>
To: Sim Zacks <sim@compulab.co.il>
Cc: pgsql-sql@postgresql.org
Subject: Re: join on next row
Date: Thu, 22 Jun 2006 02:25:07 -0400
Message-ID: <99edfc5e0606212325n1a564767o3ff43c92a6009a49@mail.gmail.com> (raw)
In-Reply-To: <e7396t$18o8$1@news.hub.org>
References: <e7396t$18o8$1@news.hub.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jlintz@gmail.com, sim@compulab.co.il
Subject: Re: join on next row
In-Reply-To: <99edfc5e0606212325n1a564767o3ff43c92a6009a49@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox