Finding 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

SQL Server 2000 Forums

Transact-SQL (2000)

Finding Duplicates

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-11 : 07:52:30

My temp table is something like this


TblA
----
Rowid        Name        CustomerName        Address1        Address2        Address3        Address4        City        PostalCode      Country
1            Karunakaran ABC                 123             Street1         Null            Null            Bangalore   560001          India
2            Karunakaran ABC                 123             Street1         Null            Null            Bangalore   560002          India
3            Arun        ABC                 456             Street2         Null            Null            Chennai   630001          India

I want to extract the records from this temptable TblA and import it to a different table Tblb, Which has similar columns like TblA and some additional columns. I need to remove the duplicate contact name entries. For E.G. - RowId 1 & 2 are same contact with different addresses, These addresses can either be problem due to Typo or some other issues. So, I'm not really bothered about the address. But I want a query which can fetch the distinct contact name for the customer for that location (based on country) and the address columns for the first record of that contact.


TblB
----
Rowid        Name        CustomerName        Address1        Address2        Address3        Address4        City        PostalCode      Country

1            Karunakaran ABC                 123             Street1         Null            Null            Bangalore   560001          India

2            Arun        ABC                 456             Street2         Null            Null            Chennai   630001          India

I can use a distinct select query to get the Name for the responding customername, but how do I fetch the addresses for that name/customername pair?. I'll also be running this against a table with 1M records.

Karunakaran

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-11 : 09:16:42

Maybe something like this?


Select	a.*
INTO	TblB
From	TblA a
JOIN	(
	SElect	min(RowID) RowID
	From	TblA
	Group by 
		[Name], Country
	) b
		ON a.rowid = b.rowid

EDIT: Sorry, I guess the group by should be Name, Customer [maybe ,country?]

the group by should be whatever you want to base the DISTINCT on.
Where is the additional column data coming from?

Be One with the Optimizer
TG

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-11 : 09:57:37

The columns will be null and will be populated at later stage. Thanks for the query. I'll work it out and let you know.

Karunakaran

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-11 : 10:36:39

Perfect TG...It worked like a charm...
The only thing is that I wanted to insert to a table which is already there in database,So I made your query to


Insert into TblB(ColumnList)
Select	a.*
From	TblA a
JOIN	(
	Select	min(RowID) RowID
	From	TblA
	Group by 
		[Name], Country
	) b
		ON a.rowid = b.rowid

I think I should be more clear on my postings..
Thanks for the query.

Karunakaran

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-16 : 04:12:59

Further revision to this query:

Apart from the above stated conditions I also want to check in TblB too for any duplicate records. I need the records from TblA which doesnt have any duplicates in TblA and has no matching records in TblB.

Any way?

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-16 : 10:19:16

There's always a way...


Insert into TblB(ColumnList)
Select	a.*
From	TblA a
JOIN	(
	Select	min(RowID) RowID
	From	TblA
	Group by 
		[Name], Country
	) b
		ON a.rowid = b.rowid
Left JOIN TblB excl
		--use whatever columns define dupes
		ON excl.[name] = a.[name]
		AND excl.country = a.country
Where	--use any column from your JOIN criteria
	excl.[name] is NULL

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources