Re: how to suppress similar rows while copying from one table to another?

[Bernt M. Johnsen]

>>>>>>>>>>>> Bernt M. Johnsen wrote (2007-11-19 10:23:05):
> 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.


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

DROP TABLE source2;





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

pgplJTEetfvM0.pgp
Description: PGP signature