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 Thu, 22 Nov 2007 13:00:47 GMT
>>>>>>>>>>>> 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

        (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

View raw message