Received: from www-uk-05.digiserve.com ([195.97.231.11]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2ELv1x88326 for ; Wed, 14 Mar 2001 16:57:01 -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 VAA29073; Wed, 14 Mar 2001 21:57:52 GMT Received: from client.archonet.com (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id D2D782A686; Wed, 14 Mar 2001 21:17:34 +0000 (GMT) From: Richard H Date: Wed, 14 Mar 2001 21:17:33 GMT Message-ID: <20010314.21173300@client.archonet.com> Subject: Re: need to join successive log entries into one To: George Young Cc: Reply-To: dev@archonet.com In-Reply-To: <01031413092300.02477@pen> References: <01031413092300.02477@pen> X-Mailer: Mozilla/3.0 (compatible; StarOffice/5.2;Linux) X-Priority: 3 (Normal) MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by mail.postgresql.org id f2ELv2x88329 X-Archive-Number: 200103/292 X-Sequence-Number: 1956 >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<< On 3/14/01, 5:24:12 PM, George Young wrote regarding [SQL] 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| > This is a log of some operations done on some runs. 'seq' is the step > within the run. > I need to produce a new table that coalesces immediately successive > operations on a run into one, e.g.: > run | start | done > 1415| 2001-01-29 12:36:55| 2001-02-07 13:02:38 > 1415| 2001-02-14 07:40:04| > 1747| 2001-02-15 09:14:39| 2001-03-01 09:02:39 > 1747| 2001-03-05 13:13:58| > 1954| 2001-02-02 20:55:39| 2001-03-02 10:17:15 Try: select run,min(start),max(done) from mytable group by run; - Richard Huxton