From derby-user-return-8121-apmail-db-derby-user-archive=db.apache.org@db.apache.org Thu Nov 22 13:01:23 2007 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 85746 invoked from network); 22 Nov 2007 13:01:23 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 22 Nov 2007 13:01:23 -0000 Received: (qmail 57486 invoked by uid 500); 22 Nov 2007 13:01:10 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 57015 invoked by uid 500); 22 Nov 2007 13:01:09 -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 57004 invoked by uid 99); 22 Nov 2007 13:01:09 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 22 Nov 2007 05:01:09 -0800 X-ASF-Spam-Status: No, hits=-1.0 required=10.0 tests=RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [192.18.98.43] (HELO brmea-mail-2.sun.com) (192.18.98.43) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 22 Nov 2007 13:01:10 +0000 Received: from dm-norway-01.uk.sun.com ([129.156.101.193]) by brmea-mail-2.sun.com (8.13.6+Sun/8.12.9) with ESMTP id lAMD0mFL015198 for ; Thu, 22 Nov 2007 13:00:49 GMT Received: from clustra.norway.sun.com (clustra.Norway.Sun.COM [129.159.119.10]) by dm-norway-01.uk.sun.com (8.13.6+Sun/8.13.6/ENSMAIL,v2.2) with SMTP id lAMD0ldv006161 for ; Thu, 22 Nov 2007 13:00:48 GMT Received: (qmail 20032 invoked from network); 22 Nov 2007 13:00:47 -0000 Received: from khepri35.norway.sun.com (129.159.112.247) by clustra.norway.sun.com with QMQP; 22 Nov 2007 13:00:47 -0000 Date: Thu, 22 Nov 2007 14:00:47 +0100 From: "Bernt M. Johnsen" To: Derby Discussion Subject: Re: how to suppress similar rows while copying from one table to another? Message-ID: <20071122130047.GB22923@khepri35.norway.sun.com> References: <473D97D7.4020700@sun.com> <473DD4F4.6040405@apache.org> <20071119092305.GB17672@khepri35.norway.sun.com> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="f2QGlHpHGjS2mn6Y" Content-Disposition: inline In-Reply-To: <20071119092305.GB17672@khepri35.norway.sun.com> Organization: Sun Microsystems User-Agent: Mutt/1.5.7i X-Virus-Checked: Checked by ClamAV on apache.org --f2QGlHpHGjS2mn6Y Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable >>>>>>>>>>>> Bernt M. Johnsen wrote (2007-11-19 10:23:05): > If there exists a column k in your source data which is unique and you > may use the aggregate function MIN on this column you may do something > like: >=20 > INSERT INTO target > (SELECT source.k, source.col1, source.col2, source.arbitrary FROM > source, > (SELECT MIN(k),col1,col2 FROM source > GROUP BY col1,col2) AS tmp(k,col1,col2) > WHERE source.k =3D tmp.k); Just for the fun of it (and not for the faint of heart), in Derby 10.4 you might use the ROW_NUMBER() windowing function to generate the key k (if you den't have one), and the expression might be written like this: INSERT INTO target (SELECT s1.k, s2.col1, s2.col2, s2.arbitrary FROM (SELECT ROW_NUMBER() ORDER by col1,col2 AS k, col1,col2,arbitraty FROM source)=20 AS s1(k,col1,col2,arbitrary), (SELECT MIN(k),col1,col2 FROM=20 (SELECT ROW_NUMBER() ORDER by col1,col2 AS k, col1,col2 FROM source)=20 AS s2(k,col1,col2), GROUP BY col1,col2) AS tmp(k,col1,col2) WHERE s1.k =3D tmp.k); (Note, I have not tested this since the ROW_NUMBER() implementation is not yet committed). --=20 Bernt Marius Johnsen, Database Technology Group,=20 Staff Engineer, Derby/Java DB Sun Microsystems, Trondheim, Norway --f2QGlHpHGjS2mn6Y Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.7 (SunOS) iD8DBQFHRX1/lFBD9TXBAPARAq/qAJ9lhgiGnd5GWLHLi/Cb+PkUXYXD0ACgtLdL eE4oF7MRnyAbafkcdFWOk/0= =UNR3 -----END PGP SIGNATURE----- --f2QGlHpHGjS2mn6Y--