Erase Duplicates - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Erase Duplicates

Author

Topic

jfm
Posting Yak Master

145 Posts

Posted - 2012-09-04 : 06:23:26

hi there,

I have my Table_1 with many duplicates. I want to delete all the duplicated rows from my Table_1

This table has 15 columns.

Any idea?

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-04 : 06:29:09

table structure and sample data would be helpful for us!

Too old to Rock'n'Roll too young to die.

jfm
Posting Yak Master

145 Posts

Posted - 2012-09-04 : 06:55:57

Ok:

Let's say that I have as columns:

last_extraction, Id, Id_number, color, buyer, seller, address, zip_code, telephone_number, country etc...

In whenever I'm using, the following query:

SELECT MAX (last_extraction) AS date
Id_number AS Id_number_1
INTO New_table
FROM Table_1
WHERE color = 'blue'
GROUP BY Id_number

SELECT * INTO Final_Table FROM Table_1 JOIN New_Table
ON New_Table.last_extraction = Table_1.last_extraction
AND New_Table.ID_Number = Table_1.ID_Number
WHERE color = 'blue'

I have duplicates in 'New_Table', because the 'last_extraction column', has some dates duplicated. I just need one of the two rows of the 'last_extraction column' from 'Final_Table'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-04 : 07:15:53

You can use one or the other of the following. Both require SQL 2005 or later:

-- 1.
;WITH cte AS
(
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY Id_Number ORDER BY last_extraction DESC) AS 
           RN
    FROM   Table_1
)
SELECT last_extraction,
       Id,
       Id_number,
       color,
       buyer,
       seller,
       ADDRESS,
       zip_code,
       telephone_number,
       country
INTO Final_Table
FROM   cte
WHERE  RN = 1;

-- 2.
SELECT b.last_extraction,
       b.Id,
       a.Id_number,
       b.color,
       b.buyer,
       b.seller,
       b.ADDRESS,
       b.zip_code,
       b.telephone_number,
       b.country
INTO Final_Table
FROM Table_1a
CROSS APPLY
(
	SELECT TOP 1 * FROM Table_1 c
	WHERE c.Id_number = b.Id_number
	ORDER BY last_extraction DESC
) b;

jfm
Posting Yak Master

145 Posts

Posted - 2012-09-04 : 09:24:13

Perfect!!

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources