<p>What is the best way to determine duplicate records in a SQL Server table?</p>

<p>For instance, I want to find the last duplicate email received in a table (table has primary key, receiveddate and email fields).</p>

<p>Sample data:</p>

<pre><code>1  01/01/2008 stuff@stuff.com
2  02/01/2008 stuff@stuff.com
3  01/12/2008 noone@stuff.com
</code></pre>

[{'answer_id': 46843, 'author': 'Rob Rolnick', 'author_id': 4798, 'author_profile': 'https://Stackoverflow.com/users/4798', 'pm_score': 0, 'selected': False, 'text': "<p>Couldn't you join the list on the e-mail field and then see what nulls you get in your result?</p>\n\n<p>Or better yet, count the instances of each e-mail address? And only return the ones with count > 1</p>\n\n<p>Or even take the email and id fields. And return the entries where the e-mail is the same, and the IDs are different. (To avoid duplicates don't use != but rather either &lt; or >.)</p>\n"}, {'answer_id': 46845, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 3, 'selected': False, 'text': '<p>something like this</p>\n\n<pre><code>select email ,max(receiveddate) as MaxDate\nfrom YourTable\ngroup by email \nhaving count(email) &gt; 1\n</code></pre>\n'}, {'answer_id': 46846, 'author': 'Michael Sharek', 'author_id': 1958, 'author_profile': 'https://Stackoverflow.com/users/1958', 'pm_score': 0, 'selected': False, 'text': '<p>Try this</p>\n\n<pre><code>select * from table a, table b\nwhere a.email = b.email\n</code></pre>\n'}, {'answer_id': 46849, 'author': 'palehorse', 'author_id': 312, 'author_profile': 'https://Stackoverflow.com/users/312', 'pm_score': 0, 'selected': False, 'text': '<pre><code>SELECT [id], [receivedate], [email]\nFROM [mytable]\nWHERE [email] IN ( SELECT [email]\n    FROM [myTable]\n    GROUP BY [email]\n    HAVING COUNT([email]) &gt; 1 )\n</code></pre>\n'}, {'answer_id': 46853, 'author': 'enigmatic', 'author_id': 443575, 'author_profile': 'https://Stackoverflow.com/users/443575', 'pm_score': 0, 'selected': False, 'text': '<p>Do you want a list of the last items?  If so you could use:</p>\n\n<pre><code>SELECT [info] FROM [table] t WHERE NOT EXISTS (SELECT * FROM [table] tCheck WHERE t.date &gt; tCheck.date)\n</code></pre>\n\n<p>If you want a list of all duplicate email address use GROUP BY  to collect similar data, then a HAVING  clause to make sure the quantity is more than 1:</p>\n\n<pre><code>SELECT [info] FROM [table] GROUP BY [email] HAVING Count(*) &gt; 1 DESC\n</code></pre>\n\n<p>If you want the last duplicate e-mail (a single result) you simply add a "TOP 1" and "ORDER BY":</p>\n\n<pre><code>SELECT TOP 1 [info] FROM [table] GROUP BY [email] HAVING Count(*) &gt; 1 ORDER BY Date DESC\n</code></pre>\n'}, {'answer_id': 46861, 'author': 'Brian', 'author_id': 700, 'author_profile': 'https://Stackoverflow.com/users/700', 'pm_score': 0, 'selected': False, 'text': "<p>If you have surrogate key, it is relatively easy to use the group by syntax mentioned in SQLMenance's post.  Essentially, group by all the fields that make two or more rows 'the same'.  </p>\n\n<p>Example pseudo-code to delete duplicate records.</p>\n\n<pre><code>Create table people (ID(PK), Name, Address, DOB)\n\nDelete from people where id not in (\nSelect min(ID) from people group by name, address, dob\n)\n</code></pre>\n"}, {'answer_id': 14757344, 'author': 'user2051770', 'author_id': 2051770, 'author_profile': 'https://Stackoverflow.com/users/2051770', 'pm_score': 1, 'selected': False, 'text': '<p>Try something like:</p>\n\n<pre><code>SELECT * FROM (\n  SELECT *, \n  ROW_NUMBER() OVER (PARTITION BY ReceivedDate, Email ORDER BY ReceivedDate, Email DESC) AS RowNumber \n  FROM EmailTable\n) a\nWHERE RowNumber = 1\n</code></pre>\n\n<p>See <a href="http://www.technicaloverload.com/working-with-duplicates-in-sql-server/" rel="nofollow">http://www.technicaloverload.com/working-with-duplicates-in-sql-server/</a></p>\n'}]
['https://Stackoverflow.com/questions/46842', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/-1/']