Get me 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 2005 Forums

Transact-SQL (2005)

Get me duplicates

Author

Topic

tech
Starting Member

32 Posts

Posted - 2011-04-26 : 08:19:31

I need a query to get me duplicates in a table - but with a difference...

The duplicates are defined as this in the same table:

ArtistID (FK)
TrackName (nvarchar)

There is another FK table/column called "GenreID" but this will be different.

I just want to find out which tracks mentioned in the table are duplicates irrespective of the GenreID - so I can "seperate" some records out to different tables and create another table and remove GenreID from the table itself.

Makes sense?

so I just want a list of duplicates in the Artist table - there will be one or more records with the SAME ArtistID and TrackName, and I think this would be the key to find duplicate entries.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-26 : 08:36:12

select ArtistID,TrackName
from ArtistTable
group by ArtistID,TrackName
having count(*) >1

Jim

Everyday I learn something that somebody else already knew

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-26 : 10:38:37

;WITH CTE
AS ( SELECT ArtistID,
TrackName,
COUNT(TrackName) OVER ( PARTITION BY TrackName ) DCount
FROM ArtistTable
)
SELECT *
FROM CTE
WHERE DCount > 1

--------------------------
http://connectsql.blogspot.com/

tech
Starting Member

32 Posts

Posted - 2011-04-26 : 13:46:38

ok, thanks. 2 different answers, 2 different results!
I believe the last one would be the one here. Many thanks folks!

X002548
Not Just a Number

15586 Posts

Posted - 2011-04-26 : 14:40:22

I Don't know what value the CTE gives you...



CREATE TABLE ArtistTable99 (
	  ArtistID	int
	, TrackName nvarchar(10)
	, GenreID	int)
GO

INSERT INTO ArtistTable99 (ArtistID, TrackName, GenreID)
SELECT 1,	'Track 1',	1 UNION ALL
SELECT 1,	'Track 1',	2 UNION ALL
SELECT 1,	'Track 1',	3 UNION ALL
SELECT 2,	'Track 2',	1 UNION ALL
SELECT 3,	'Track 3',	1 UNION ALL
SELECT 3,	'Track 3',	3 UNION ALL
SELECT 4,	'Track 4',	1
GO

;WITH CTE
AS ( SELECT ArtistID,TrackName, COUNT(TrackName) OVER ( PARTITION BY TrackName ) DCount
	   FROM ArtistTable99)
	   
SELECT * FROM CTE WHERE DCount > 1

  SELECT ArtistID,TrackName, COUNT(*)
    FROM ArtistTable99
GROUP BY ArtistID,TrackName
   HAVING COUNT(*) >1
GO

DROP TABLE ArtistTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources