Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2EN1kx97965 for ; Wed, 14 Mar 2001 18:01:46 -0500 (EST) (envelope-from reedstrm@rice.edu) Received: by rice.edu via sendmail from stdin id (Debian Smail3.2.0.102) for pgsql-sql@postgresql.org; Wed, 14 Mar 2001 17:01:43 -0600 (CST) Date: Wed, 14 Mar 2001 17:01:43 -0600 From: "Ross J. Reedstrom" To: pgsql-sql@postgresql.org Subject: Re: need to join successive log entries into one Message-ID: <20010314170143.A30281@rice.edu> Mail-Followup-To: pgsql-sql@postgresql.org References: <01031413092300.02477@pen> <20010314.21173300@client.archonet.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.0i In-Reply-To: <20010314.21173300@client.archonet.com>; from dev@archonet.com on Wed, Mar 14, 2001 at 09:17:33PM +0000 X-Archive-Number: 200103/296 X-Sequence-Number: 1960 On Wed, Mar 14, 2001 at 09:17:33PM +0000, Richard H wrote: > > > 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; select run,min(start),max(done) from mytable group by run UNION ALL select run,start,NULL from mytable where done is null; Assuming that any given run has only one open step. Ross