UnDup SQL pattern

    /*
    || Recording this here for reference
    || it's a pattern that will repeat
    || In theory you can do without creating
    || interim table -- in practice I've found
    || otherwise...
    || 'min_record_id' is assumed to be a 
    || unique sequential number assigned to
    || records as they come in ...
    */
    CREATE TABLE min_record_id (record_id bigint(19));
   
CREATE UNIQUE INDEX min_record_id_PK on min_record_id(record_id);
    INSERT INTO min_record_id
        SELECT *
          FROM (-- ilv
                   SELECT MIN(record_id) as record_id
                     FROM dup_file

                    GROUP
                       BY {uniquely identifying items}
               )

                  ilv
    ;
    CREATE TABLE file_unique
    SELECT  df.*
      FROM  dup_file df 

            inner join 
            min_record_id mri 
            on df.record_id
            =  mri.record_id
    ;
    DROP TABLE min_record_id;

No comments:

Favorite Tweets