Post reply

Delete duplicates records except 1st one

  • April 4, 2006 at 11:02 am

    #168430

    I have a table with identity column and primary key(pro_id) but had a few thousands records with duplicates in 4 columns(date portion of pro_date, pro_seq, pro_mix, & pro_tank).  pro_date is a timestamp so it's always unique but our users want to put a unique constraint on the date portion of pro_date, pro_seq, pro_mix and pro_tank.  First I've to clean up the file to delete those that are duplicated except the first occurence of the combination.  Here is an example

    pro_id        pro_date                pro_seq pro_mix pro_tank

    6065   2004-10-27 02:31:04.000    4      XXX101   TK1

    6067   2004-10-27 11:12:08.000    4      XXX101   TK1

    We want to delete all except the first recods.  Is there an easy way to do it so I can clean up the table before I add the unique constraint to it?  Any helps would be greatly appreciated.  Thanks

    GLO

  • April 4, 2006 at 11:19 am

    #630763

    this should get you all the first occurences...if it doesn't, please post some more sample data...

    select a.* from myTable a
inner join
(select min(pro_id) pro_id from myTable 
group by pro_seq, pro_mix, pro_tank)b
on a.pro_id = b.pro_id







    **ASCII stupid question, get a stupid ANSI !!!**

  • April 4, 2006 at 12:23 pm

    #630783

    Thank you very much.  It works.

    GLO

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply