title: Delete duplicate records only on condition in sql server
body: <p>I want to delete duplicates for example I want to delete this row "Test2, 321, 0" because there is a duplicate, but in row 1 and 2 I only want to delete row two because the type id is higher then the current duplicate in row one.</p>
  
  <p>This is my table</p>
  
  <pre>
  ID, Name, RecordNum, Type
  --------------------------
  1, Test, 123, 0
  2, Test, 123, 1
  3, Test2, 321, 0
  4, Test2, 321, 0
  </pre>
  
  <p>I can delete duplicate in row 4 using this query. but I cannot seem to figure out how to delete row 2 because row 1 is the same but type number is lower. and if the type number is 2 it wins and you have to delete any duplicates with 0 and 1 type numbers that have the same name and recordnum.</p>
  
  <pre><code>WITH dup2 as ( 
    SELECT Name
         , RecordNum
         , Type ROW_NUMBER() OVER(PARTITION BY Name, RecordNum, Type ORDER BY ID ASC) AS NumOfDups 
      FROM MyTbale) 
    delete FROM dup2 WHERE NumOfDups &gt; 1
  </code></pre>