Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Database (http://www.go4expert.com/forums/database-forum/)
-   -   please Help: Help removing all but the FIRST of duplicate records (http://www.go4expert.com/forums/help-help-removing-duplicate-records-t19098/)

wgre0111 20Aug2009 02:49

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

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, [b]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!

wgre0111 20Aug2009 02:50

Re: please Help: Help removing all but the FIRST of duplicate records
 
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

xpi0t0s 20Aug2009 12:21

Re: please Help: Help removing all but the FIRST of duplicate records
 
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>.

naimish 20Aug2009 12:33

Re: please Help: Help removing all but the FIRST of duplicate records
 
Hope he is using SQL ;) lol

xpi0t0s 20Aug2009 15:10

Re: please Help: Help removing all but the FIRST of duplicate records
 
well he referred to SQL in both posts so I would say that's a yes.

nimesh 23Aug2009 03:19

Re: please Help: Help removing all but the FIRST of duplicate records
 
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


All times are GMT +5.5. The time now is 22:12.