title: SQL to remove rows with duplicated value while keeping one
body: <p>Say I have this table</p>
  
  <pre><code>id | data | value
  -----------------
   1 |   a  |   A
   2 |   a  |   A
   3 |   a  |   A
   4 |   a  |   B
   5 |   b  |   C
   6 |   c  |   A
   7 |   c  |   C
   8 |   c  |   C
  </code></pre>
  
  <p>I want to remove those rows with duplicated value for each data while keeping the one with the min id, e.g. the result will be</p>
  
  <pre><code>id | data | value
  -----------------
   1 |   a  |   A
   4 |   a  |   B
   5 |   b  |   C
   6 |   c  |   A
   7 |   c  |   C
  </code></pre>
  
  <p>I know a way to do it is to do a union like:</p>
  
  <pre><code>SELECT 1 [id], 'a' [data], 'A' [value] INTO #test UNION SELECT 2, 'a', 'A'
  UNION SELECT 3, 'a', 'A' UNION SELECT 4, 'a', 'B'
  UNION SELECT 5, 'b', 'C' UNION SELECT 6, 'c', 'A'
  UNION SELECT 7, 'c', 'C' UNION SELECT 8, 'c', 'C'
  
  SELECT * FROM #test WHERE id NOT IN (
      SELECT MIN(id) FROM #test
      GROUP BY [data], [value]
      HAVING COUNT(1) &gt; 1
      UNION
      SELECT MIN(id) FROM #test
      GROUP BY [data], [value]
      HAVING COUNT(1) &lt;= 1
  )
  </code></pre>
  
  <p>but this solution has to repeat the same group by twice (consider the real case is a massive group by with > 20 columns)</p>
  
  <p>I would prefer a simpler answer with less code as oppose to complex ones. Is there any more concise way to code this?</p>
  
  <p>Thank you</p>