From derby-user-return-8092-apmail-db-derby-user-archive=db.apache.org@db.apache.org Mon Nov 19 13:07:46 2007 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 85154 invoked from network); 19 Nov 2007 13:07:46 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 19 Nov 2007 13:07:46 -0000 Received: (qmail 13989 invoked by uid 500); 19 Nov 2007 13:07:32 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 13963 invoked by uid 500); 19 Nov 2007 13:07:32 -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 13951 invoked by uid 99); 19 Nov 2007 13:07:32 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 19 Nov 2007 05:07:32 -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 (athena.apache.org: local policy) Received: from [192.18.43.21] (HELO sca-ea-mail-3.sun.com) (192.18.43.21) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 19 Nov 2007 13:07:20 +0000 Received: from dm-norway-01.uk.sun.com ([129.156.101.193]) by sca-ea-mail-3.sun.com (8.13.6+Sun/8.12.9) with ESMTP id lAJCkxtN015845 for ; Mon, 19 Nov 2007 12:46:59 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 lAJCkwOE006578 for ; Mon, 19 Nov 2007 12:46:58 GMT Received: (qmail 5872 invoked from network); 19 Nov 2007 12:46:58 -0000 Received: from khepri35.norway.sun.com (129.159.112.247) by clustra.norway.sun.com with QMQP; 19 Nov 2007 12:46:58 -0000 Date: Mon, 19 Nov 2007 13:46:57 +0100 From: "Bernt M. Johnsen" To: Derby Discussion Subject: Re: how to suppress similar rows while copying from one table to another? Message-ID: <20071119124657.GC17672@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="xesSdrSSBC0PokLI" 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 --xesSdrSSBC0PokLI 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): > Hi, >=20 > >>>>>>>>>>>> Dag H. Wanvik wrote (2007-11-17 02:37:27): > > Tried in vain to come up with a clever single INSERT, though. >=20 > Depending on the data, this might be done in SQL with a "clever single > INSERT". >=20 > 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); >=20 > Note that the term "first" has no meaning in SQL unless you assign > some kind of order to your data. In this example, the numeric value k > is considered to define the order, and thus MIN(k) will be the "first" > value. If you don't have a unique column k, you may of course create a intermediate table with one with an generated identity column (This assumes that you have no preferred order of your rows). Assume source has four integer columns: col1, col2 and arbitrary, the whole operation should go like this: CREATE TABLE source2 (col1 INTEGER, col2 INTEGER, arbitrary INTEGER,=20 k INTEGER GENERATED ALWAYS AS IDENTITY); INSERT INTO source2(col1,col2,arbitrary) (SELECT * from SOURCE); INSERT INTO target (SELECT source2.col1, source2.col2, source2.arbitrary FROM source2, (SELECT MIN(k),col1,col2 FROM source2 GROUP BY col1,col2) AS tmp(k,col1,col2) WHERE source2.k =3D tmp.k); DROP TABLE source2; --=20 Bernt Marius Johnsen, Database Technology Group,=20 Staff Engineer, Derby/Java DB Sun Microsystems, Trondheim, Norway --xesSdrSSBC0PokLI Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.7 (SunOS) iD8DBQFHQYXBlFBD9TXBAPARAnI/AKCIgUFxwpgAIgL2lgbHzDXe177jGACfTEOc +ozIo5u5ISUFW5oqmlJAxVw= =o4De -----END PGP SIGNATURE----- --xesSdrSSBC0PokLI--