title: Using DISTINCT in where clause subquery to find unique column combination
body: <p>I've been tasked with identifying and removing the duplicate entries from a really messy asset list. It's a mess because, over the years, different users / developers have tried to use alternate unique identification setups that, because of the absence of any primary keys, kind of worked if you weren't paying attention to the data.</p>
  <p>I can find unique rows (in a broad sense) by using:</p>
  <pre><code>SELECT DISTINCT
      AssetId, AssetNo, PPH, Testdate
  FROM dbo.AssetList;
  </code></pre>
  <p>And now I'm trying to expand the query to allow me to select every column in the dataset. I thought this would work:</p>
  <pre><code>SELECT *
  FROM dbo.AssetList
  WHERE AssetId IN (
      SELECT DISTINCT
          AssetId, AssetNo, PPH, Testdate
      FROM dbo.AssetList
  );
  </code></pre>
  <p>But I was wrong. Can anyone explain to me how I can achieve this in SQL Server?</p>