Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 10566 invoked from network); 16 Nov 2007 17:36:26 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 16 Nov 2007 17:36:26 -0000 Received: (qmail 84600 invoked by uid 500); 16 Nov 2007 17:36:11 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 84578 invoked by uid 500); 16 Nov 2007 17:36:11 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 84567 invoked by uid 99); 16 Nov 2007 17:36:11 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 16 Nov 2007 09:36:11 -0800 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy includes SPF record at spf.trusted-forwarder.org) Received: from [68.142.229.93] (HELO smtp112.sbc.mail.re2.yahoo.com) (68.142.229.93) by apache.org (qpsmtpd/0.29) with SMTP; Fri, 16 Nov 2007 17:36:00 +0000 Received: (qmail 20393 invoked from network); 16 Nov 2007 17:35:52 -0000 Received: from unknown (HELO ?9.72.133.48?) (ddebrunner@sbcglobal.net@32.97.110.142 with plain) by smtp112.sbc.mail.re2.yahoo.com with SMTP; 16 Nov 2007 17:35:52 -0000 X-YMail-OSG: xv_S.K8VM1lHc2eJ8UXyBd62YHQ842e78vPFOkSCBf.qVkMBrpdEkrV.ajc6kxSuFiqJE7cjPJhOHm_J49wgwc0Wg9aTDfiPeLl4RX3YN2BPvVJ5A6ldfkWb5zwyliiV15KQRWaAs3XEWw-- Message-ID: <473DD4F4.6040405@apache.org> Date: Fri, 16 Nov 2007 09:35:48 -0800 From: Daniel John Debrunner User-Agent: Thunderbird 2.0.0.9 (Windows/20071031) MIME-Version: 1.0 To: Derby Discussion Subject: Re: how to suppress similar rows while copying from one table to another? References: <473D97D7.4020700@sun.com> In-Reply-To: <473D97D7.4020700@sun.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org Rick Hillegas wrote: > One way to solve this problem would be to put a filtering function in > the WHERE clause of the driving SELECT statement. Something like this: > > INSERT INTO targetTable > SELECT * FROM sourceTable > WHERE isFirstInstance( tastyColumn1, tastyColumn2 ) = 1 > > Here isFirstInstance is a function which returns 1 the first time it > sees a given key combination and returns 0 on all subsequent sightings. > You, of course, have to write this user function. Any thoughts on how to do that? Since such calls are stateless how would you identify it's the first call for a query execution? Dan.