title: How find duplicates in a table with no primary key or ID field?
body: <p>I've inherited a SQL Server database that has duplicate data in it. I need to find and remove the duplicate rows. But without an id field, I'm not sure how to find the rows. </p>
  
  <p>Normally, I'd compare it with itself using a <code>LEFT JOIN</code> and check that all fields are the same except the ID field would be <code>table1.id &lt;&gt; table2.id</code>, but without that, I don't know how to find duplicates rows and not have it also match on itself.</p>
  
  <p>TABLE:</p>
  
  <pre><code>productId int not null,
  categoryId int not null,
  state varchar(255) not null,
  dateDone DATETIME not null
  </code></pre>
  
  <p>SAMPLE DATA</p>
  
  <pre><code>1, 3, "started", "2016-06-15 04:23:12.000"
  2, 3, "started", "2016-06-15 04:21:12.000"
  1, 3, "started", "2016-06-15 04:23:12.000"
  1, 3, "done", "2016-06-15 04:23:12.000"
  </code></pre>
  
  <p>In that sample, only rows 1 and 3 are duplicates.</p>
  
  <p>How do I find duplicates?</p>