Received: from www-uk-05.digiserve.com ([195.97.231.11]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2F85lx68533 for ; Thu, 15 Mar 2001 03:05:47 -0500 (EST) (envelope-from dev@archonet.com) Received: from mainbox.archonet.com (mwynhau.demon.co.uk [193.237.186.96]) by www-uk-05.digiserve.com (8.9.1/8.9.1) with ESMTP id IAA03370; Thu, 15 Mar 2001 08:06:35 GMT Received: from archonet.com (ibook.archonet.com [192.168.1.32]) by mainbox.archonet.com (Postfix) with ESMTP id EDBC72A681; Thu, 15 Mar 2001 07:39:43 +0000 (GMT) Message-ID: <3AB071A6.E07AB2@archonet.com> Date: Thu, 15 Mar 2001 07:39:18 +0000 From: Richard Huxton X-Mailer: Mozilla 4.7C-CCK-MCD {C-UDP; EBM-APPLE} (Macintosh; I; PPC) X-Accept-Language: en MIME-Version: 1.0 To: George Young Cc: pgsql-sql@postgresql.org Subject: Re: need to join successive log entries into one References: <20010314.21173300@client.archonet.com> <01031417344206.02477@pen> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200103/303 X-Sequence-Number: 1967 George Young wrote: > > On Wed, 14 Mar 2001, you wrote: > > On 3/14/01, 5:24:12 PM, George Young wrote regarding [SQL] > > I need to join successive log entries into one: > > > I have a table like: > > > > > run | seq | start | done > > > 1415| 261| 2001-01-29 12:36:55| 2001-02-07 13:02:38 > > > 1415| 263| 2001-02-14 07:40:04| 2001-02-15 16:05:04 > > > 1415| 264| 2001-02-16 16:05:05| 2001-03-08 16:34:03 > > > 1415| 265| 2001-03-08 16:34:04| > > > > Try: > > > > select run,min(start),max(done) from mytable group by run; > > Alas, this combines *all* entries for a given run, not just those that > are imediately adjacent (in time, or by 'seq' number)... I thought it was complicated, then I thought it was easy. Looks like I was right first time. I was thinking that some huge self-join might do it, but I can't see how to go beyond a run of two adjacent entries. The only thing I can think of is to add a "batch" column and build a trigger to set it as data is inserted. I'm assuming the entries are put in one at a time and in order. That way you just need to look at the last entry to determine if the new one is in the same batch. Any use? - Richard Huxton > -- > George Young, Rm. L-204 gry@ll.mit.edu > MIT Lincoln Laboratory > 244 Wood St. > Lexington, Massachusetts 02420-9108 (781) 981-2756