Duplicate Records - 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

Duplicate Records

Author

Topic

tim8w
Starting Member

10 Posts

Posted - 2014-02-13 : 13:49:58

Hi,
I have a table that contains some duplicate records. The entire record is not duplicated, but there are two fields which if both are duplicated, result in what I call a duplicate record. I can see that I have these duplicates by doing the following:

SELECT DISTINCT ReferenceNumber, ShipToCompanyID
FROM         tabMedia
WHERE     (MediaTypeID = '1') AND (FinishDate > '2011-01-01')
GROUP BY ReferenceNumber, ShipToCompanyID
HAVING      (COUNT(ReferenceNumber) > 1) AND (COUNT(ShipToCompanyID) > 1)
ORDER BY ReferenceNumber

but of course, this only returns two fields. I need the entire record returned where the duplicate exists... Any ideas?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-13 : 16:44:04

What version of SQL server are you using? Assuming you are using a relatively recent version, you can make use the ROW_NUMBER() function. Alternatively, you can just make the aggregate a derived table a join make to the base table:

SELECT
	Media.*
FROM
	tabMedia AS Media
INNER JOIN
	(
		SELECT 
			ReferenceNumber
			,ShipToCompanyID
		FROM 
			tabMedia
		WHERE
			MediaTypeID = '1' 
			AND FinishDate > '2011-01-01'
		GROUP BY 
			ReferenceNumber
			,ShipToCompanyID
		HAVING
			COUNT(*) > 1
	) AS T
	ON Media.ReferenceNumber = T.ReferenceNumber
	AND Media.ShipToCompanyID = T.ShipToCompanyID

tim8w
Starting Member

10 Posts

Posted - 2014-02-13 : 17:22:32

Lamprey,
Thanks. That worked great! Should have been obvious I guess, but I'm still learning sub-queries...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources