Delete Duplicate Records

prasanta2expert · December 4th, 2006, 06:58 AM

Hi Frnds,

I have another query. Suppose I have following data.

Name Age
---- -----
X 20
A 35
M 19
X 27
N 80
X 41

Now what I have to do is, delete those rows which have same Name field except the highest AGE. For example,(X,20) and (X,27) will be deleted and (x,41) will remain. How to do this?? Please help.

Jeff Moden · December 4th, 2006, 09:49 AM

Does this table have a Primary Key? What is it?

--Jeff Moden

prasanta2expert · December 5th, 2006, 01:06 AM

Thanks for ur reply. No, this table does not have any primary key.

robprell · December 5th, 2006, 03:44 PM

I would put this in a cursor and simply cursor through them. Sort by age descending and delete eacy row where the previous column has the same value for Name.

Jeff Moden · December 6th, 2006, 03:19 AM

Cursor? Didn't know we were allowed to swear in this forum :D

The following will do the trick nicely... without a cursor.

Code:

 DELETE m
   FROM yourtable m,
        (SELECT DISTINCT
                t1.Name,t1.Age
           FROM yourtable t1,
                yourtable t2
          WHERE t1.Name = t2.Name
            AND t1.Age  < t2.Age
        ) d
  WHERE m.Name = d.Name
    AND m.Age  = d.Age

--Jeff Moden

robprell · December 14th, 2006, 03:27 PM

when two are equal will they both be deleted with this code?

Jeff Moden · December 14th, 2006, 11:08 PM

Yes and no... if the two ages are not the oldest, then yes. If the two ages are the oldest, it's a tie and neither will be deleted.

--Jeff Moden

robprell · December 15th, 2006, 07:39 PM

Thus my point. My solution will work 100% of the time, yours will work almost all of the time.

Jeff Moden · December 15th, 2006, 08:09 PM

Heh... What would work better is a good primary key! The code I posted will work fine in ALL cases if the user has some column that deals with chronology a bit (I forgot to ask THAT question)... an auto-numbering column or a date entered would work fine.

If you don't mind, please post your cursor solution so I can test it against a million row table, eh? Thanks.

--Jeff Moden

robprell · December 15th, 2006, 10:44 PM

Cursors generally when writen correctly run about as fast as a query depending on what your doing and if you need a cursor. The last three co-workers who disliked cursors and suggested we don't use cursors for solutions I suggested using cursors were all shown how my cursor ran faster than their solution. If you understand what SQL is doing when you use a cursor vs not use a cursor and when to use them they are usually faster than other solutions. When you write a cursor you generally table scan a table only once. I would bet you as others in the past if we used a cursor in a million row table for this it would be faster. Further there is a more important point. The cursor would provide the correct results, your solution would usually provide the correct result. What is more important the fastest solution or the fastest correct solution in your world. MS developers generally prefer just the fastest solution. Your point is odd in the previous post. If you had a primary key you would not have the problem.