1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

please Help: Help removing all but the FIRST of duplicate records

Discussion in 'Database' started by wgre0111, Aug 19, 2009.

  1. wgre0111

    wgre0111 New Member


    I am trying to remove duplicate records from a table but preserving one of them using temp tables/etc. Pretty much removing duplicates but leaving one of the identical sets.

    Example DATA would like like:

    col1, col2, col3
    20, 1090, bad
    22, 1090, bad
    25, 1090, bad
    28, 1091, good
    2350, 2444, bad
    2353, 2444, bad
    2370, 2444, bad
    3000, 5000, good
    3005, 5001, good

    AFTER the DELETE sql (which I need help with)
    example data (based off of above) should look like

    col1, col2[/ col3
    20, 1090, bad
    28, 1091, good
    2350, 2444, bad
    3000, 5000, good
    3005, 50001, good

    Please notice that one of each of the sets that had duplicates remains.

    The records the new sql would need to focus on would be records on col2 With counts(col2) >1 should not have two col2's with data like 1090. All but one of them would need to be deleted. Doesnt matter which one is left.

    Please help!
  2. wgre0111

    wgre0111 New Member

    Something I would like to try is building a temp table and just comparing original table to the temp table... Dont know how to do this in informix though.... Here is a sketch.

    delete Table1
    from origtable Table1, temp_table Table2
    where Table1.col2 = Table2.col2
    and Table1.col1 > table2.col2
  3. xpi0t0s

    xpi0t0s Mentor

    Don't know if you can do it in a single query but if you SELECT DISTINCT col2 in a cursor, then loop over that cursor you can SELECT MIN(col1) ... WHERE col2=<cursor value>.
  4. naimish

    naimish New Member

    Hope he is using SQL ;) lol
  5. xpi0t0s

    xpi0t0s Mentor

    well he referred to SQL in both posts so I would say that's a yes.
  6. nimesh

    nimesh New Member

    I think he's referring to generic sql, and the tool he referred is informix.
    Does informix has it's own sql language, or it uses T-SQL or PL/SQL

Share This Page