save one from each group of duplicates

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)

save one from each group of duplicates

Author

Topic

superjen26
Starting Member

1 Post

Posted - 2011-09-08 : 20:30:24

Hi I need a little help in this query
I need to save one from each group of duplicate records that need to delete

ex.
there are 10 records that are duplicated and I need to eliminate 9 of them and save only 1 record..

hoping for fast help, :)

annyeong

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 08:22:49

do you have a unique valued column in your table like id column or datetime (timestamp) column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2011-09-15 : 10:44:29

Visakh I have a similar reqmt. I have a unique id col

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 11:38:14

just group by set of columns which identifies a duplicate set and the take MIN() or MAX() of uniquecol and join to main table.

ie. if you table has duplicates based on columns col1 and col2 then


SELECT t.columns.....
FROM Table t
INNER JOIN (SELECT Col1,Col2,MAX(uniqueid) as Latest
            FROM Table
            GROUP BY Col1,Col2) t1
ON t1.Col1= t.Col1
AND t1.Col2 = t.Col2
AND t1.Latest = t.uniqueid

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources