Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  SQL 2000 Duplicates

Author  Topic  mpkopa Starting Member 1 Post Posted - 2001-02-06 : 14:07:38 I have a table (Products - ID,Name,Category,DateEntered) that has multiple duplicates of the same Name item. Some records are repeated as many as 20 or more times. My dilema - I need to keep the top 3 records by DateEntered (Most recent entries)for any Name that is duplicated. Example: id Name Category DateEntered 1 Apples Granny 1/28/2001 2 Apples Yellow 1/02/2001 3 Apples Macs 12/31/2000 4 Apples Delicious 12/05/2000 5 Apples Green 12/01/2000 6 Oranges Florida 1/22/2001 7 Oranges California 1/12/2001 8 Oranges Navel 1/02/2001 9 Oranges Sweet 12/22/2000 Yada Yada... The recordset I've been handed has over 8M records. How can I walk down thru the recordset telling SQL to keep only the most recently entered records, but ONLY the most recent 3 duplicate records, and delete all the others for that particular Name - leaving the top 3 records only for duplicates that have more than 3 entries? EX:Apples 1,2,3 and Oranges 6,7,8 Could you possibly provide some code so I can begin to see the process? I'm Stumped! Thanks for your help Mike