Need to eliminate duplicate entries

Question

Post reply

Need to eliminate duplicate entries

Mohamed Asane

SSC Veteran

Points: 222
More actions
December 7, 2009 at 5:00 am

#135801

Hi Dude,
I have one table as follow:
#TableA
-------
NoName
------------
1Edwin
1Edwin
2Raja
2Raja
2Raja
3Rasul
3Rasul
3Rasul
3Rasul
4Katti
4Katti
I need to remove duplicate entries from the above table
your suggestion will be greatly appreciated..
-M

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

SELECT DISTINCT *

INTO #TABLEB

FROM #TABLEA

SELECT No, Name

INTO #TABLEB

FROM #TABLEA

GROUP BY No, Name

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 2

Asane (12/7/2009)
Hi Dude,
I have one table as follow:
#TableA
-------
NoName
------------
1Edwin
1Edwin
2Raja
2Raja
2Raja
3Rasul
3Rasul
3Rasul
3Rasul
4Katti
4Katti
I need to remove duplicate entries from the above table
your suggestion will be greatly appreciated..
-M

Hi,

Here's what I propose, I think it's one of the best solution for removing duplicates.

--1 Edwin

--2 Raja

--3 Rasul

--4 Katti

--=== If the table exists, we need to drop it before we start

IF EXISTS ( SELECT OBJECT_ID('TableA') )

BEGIN

DROP TABLE TABLEA

END

GO

--=== Create the test table, to show how to remove duplicates

CREATE TABLE TableA ( ID INT, NAME VARCHAR(10) ) ;

GO

INSERT INTO TableA

( ID ,

Name

)

SELECT 1 ,

'Edwin'

UNION ALL

SELECT 1 ,

'Edwin'

UNION ALL

SELECT 2 ,

'Raja'

UNION ALL

SELECT 2 ,

'Raja'

UNION ALL

SELECT 2 ,

'Raja'

UNION ALL

SELECT 3 ,

'Rasul'

UNION ALL

SELECT 3 ,

'Rasul'

UNION ALL

SELECT 3 ,

'Rasul'

UNION ALL

SELECT 3 ,

'Rasul'

UNION ALL

SELECT 4 ,

'Katti'

UNION ALL

SELECT 4 ,

'Katti' ;

--=== Query the temp Data

SELECT *

FROM TableA ;

--=== Use a CTE to number the duplicate rows, so we know which to erase

WITH cteDup

AS ( SELECT ID ,

NAME ,

ROW_NUMBER() OVER ( PARTITION BY ID, Name ORDER BY ID ASC, NAME ASC ) AS RowNum

FROM tableA

)

--=== Now we that we have numberized the rows we need to delete, delete all the ones that are duplicate (RowNum > 1)

DELETE FROM cteDup

WHERE RowNum > 1 ;

--=== Validate the rows have been deleted;

SELECT *

FROM TableA ;

--=== Clean ourselves up

DROP TABLE TableA;

I hope that helps!

Cheers,

J-F

Mohamed Asane SSC Veteran Points: 222 More actions · Answer 3

Hi,

--Chris

Your solution will eliminate duplicates, only if all the columns are contain the same value..

--Bergeron

your solution is fulfilling my requirement.

Thanking you both for giving such valuable solution..

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Mohamed Asane (12/7/2009)
only if all the columns are contain the same value..

They are, aren't they?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mohamed Asane SSC Veteran Points: 222 More actions · Answer 5

Chris Morris-439714 (12/8/2009)
Mohamed Asane (12/7/2009)
only if all the columns are contain the same value..
They are, aren't they?

hi,

Assume that TableA contain ID,name,score

ID '5' is having multiple records with same name but different scores.

Ex:

IDnamescore

-----------------------

5chindu100

5chindu200

5chindu100

5chindu500

My requirement is wanna retain only one record based on ID and delete remain records irrespective of scores.

Infact I doesn't elaborated my requirement.. sorry chris...

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 6

In SQL Server 2005, you should make use of row_number() function

Also see what you can do with it

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 7

Madhivanan-208264 (12/8/2009)
In SQL Server 2005, you should make use of row_number() function
Also see what you can do with it
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

This is exactly the solution I posted, using row_number(). It's the best way to actually eliminate duplicates.

Cheers,

J-F