Received: from echna.ne.jp (echna.ne.jp [210.175.52.4]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9MGHtP17560 for ; Mon, 22 Oct 2001 12:17:56 -0400 (EDT) (envelope-from rk73@echna.ne.jp) Received: from VIP333 [210.175.55.217] by echna.ne.jp with ESMTP (SMTPD32-6.06) id A8BE24DA026C; Tue, 23 Oct 2001 00:18:22 +0900 Date: Tue, 23 Oct 2001 00:19:00 +0900 From: Masaru Sugawara To: pgsql-sql@postgresql.org Subject: Re: Identifying obsolete values In-Reply-To: <200110171517.RAA13051@rodos> References: <200110171517.RAA13051@rodos> Errors-To: Message-Id: <20011022234357.341F.RK73@echna.ne.jp> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Becky! ver. 2.00.06 X-Archive-Number: 200110/294 X-Sequence-Number: 5247 On Wed, 17 Oct 2001 17:17:44 METDST Haller Christoph wrote: > I use the second table to identify the actual resp. obsolete ones within the first table. > > DELETE FROM advncd_tempreftime; > INSERT INTO advncd_tempreftime > SELECT timepoint,mid,lid,sid,MAX(entrancetime) FROM advncd_onfvalue > GROUP BY timepoint,mid,lid,sid ; > > SELECT o.sid,o.timepoint,o.lid,o.mid,o.value FROM advncd_onfvalue o > WHERE EXISTS > (SELECT * FROM advncd_tempreftime t WHERE > o.timepoint = t.timepoint AND > o.mid = t.mid AND > o.lid = t.lid AND > o.sid = t.sid AND > o.entrancetime = t.entrancetime > ) ; Hi, It seems that a series of the operations can be unified. SELECT o.sid,o.timepoint,o.lid,o.mid,o.value FROM advncd_onfvalue as o WHERE EXISTS (SELECT t.timepoint, t.mid, t.lid, t.sid FROM advncd_onfvalue as t GROUP BY t.timepoint, t.mid, t.lid, t.sid HAVING o.timepoint = t.timepoint AND o.mid = t.mid AND o.lid = t.lid AND o.sid = t.sid AND o.entrancetime = MAX(t.entrancetime) ) ; By the way, a mail server have been downed ? Regards, Masaru Sugawara