Partial duplicates and select list

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)

Partial duplicates and select list

Author

Topic

rickrat
Starting Member

2 Posts

Posted - 2008-04-22 : 03:01:25

I have a table with DiscNo, Artist, Title and other fields. I would like to find all duplicate records with the same artist/title and with the first 3 characters of the discnumber. e.g.


SELECT Artist, Title Into #TempArtistTitle FROM MediaFile GROUP BY Artist, Title
HAVING COUNT(SubString(DiscNo, 0, 3)) > 1

SELECT MediaFile.DiscNo, MediaFile.Artist, MediaFile.Title FROM MediaFile RIGHT OUTER JOIN #TempArtistTitle 
ON MediaFile.Title = #TempArtistTitle.Title AND MediaFile.Artist = #TempArtistTitle.Artist 
ORDER BY Artist, Title, DiscNo

Drop TABLE #TempArtistTitle
GO

See, if the first 3 characters of the disc number is the same, it is the same manufacturer. This query works somewhat, although it returns records that the discnumber is unique too. Like below, the LG disc number shouldn't be returned, as there is only one record for that artist/title.


SC8151-10 - Garth Brooks - Friends In Low Places
SC8125-04 - Garth Brooks - Friends In Low Places
LG5003-07 - Garth Brooks - Friends In Low Places

Could someone help me please?

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-04-22 : 03:29:14

try this,

Select * From (
SELECT DiscNo, Artist, title,
Row_Number() over(Partition by Artist, title, SubString(DiscNo, 0, 3) order by DiscNo) as 'Row'
FROM MediaFile
) A
WHere Row > 1

rickrat
Starting Member

2 Posts

Posted - 2008-04-22 : 03:31:17

quote:
Originally posted by PeterNeo

try this,

Select * From (
SELECT DiscNo, Artist, title,
Row_Number() over(Partition by Artist, title, SubString(DiscNo, 0, 3) order by DiscNo) as 'Row'
FROM MediaFile
) A
WHere Row > 1

I love you! This works, but as my fixed post above, it still returns records with only one instance of a disc number too.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 04:15:46

What about this:-

SELECT MediaFile.DiscNo, MediaFile.Artist, MediaFile.Title FROM MediaFile 
INNER JOIN (
SELECT SubString(DiscNo, 0, 3) AS DiscNo,Artist, Title Into #TempArtistTitle FROM MediaFile 
GROUP BY SubString(DiscNo, 0, 3),Artist, Title
HAVING COUNT(*) > 1
)temp
ON MediaFile.Title = temp.Title 
AND MediaFile.Artist = temp.Artist 
AND SubString(MediaFile.DiscNo, 0, 3)=t.DiscNo
ORDER BY MediaFile.DiscNo, MediaFile.Artist, MediaFile.Title

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources