db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: how to suppress similar rows while copying from one table to another?
Date Mon, 19 Nov 2007 09:23:05 GMT
Hi,

>>>>>>>>>>>> Dag H. Wanvik wrote (2007-11-17 02:37:27):
> Tried in vain to come up with a clever single INSERT, though.

Depending on the data, this might be done in SQL with a "clever single
INSERT".

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:

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 = tmp.k);

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.

Bernt

-- 
Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Derby/Java DB
Sun Microsystems, Trondheim, Norway

Mime
View raw message