>>>>>>>>>>>> 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: > > 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);
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)
AS s1(k,col1,col2,arbitrary),
(SELECT MIN(k),col1,col2 FROM
(SELECT ROW_NUMBER() ORDER by col1,col2 AS k,
col1,col2 FROM source)
AS s2(k,col1,col2),
GROUP BY col1,col2) AS tmp(k,col1,col2)
WHERE s1.k = tmp.k);
(Note, I have not tested this since the ROW_NUMBER() implementation is
not yet committed).
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Derby/Java DB
Sun Microsystems, Trondheim, Norway
pgp1EHQYnyxvo.pgp
Description: PGP signature
