title: SQL Delete subquery, removed duplicates records
body: <p>I am trying to write a query to removed duplicates records from the following table (valid_columns) and keep only the records with the lowest possible [order] number.</p>
  
  <p>For example in the following table I would like to remove duplicate rows, region 2,3 and job 3 and keep the records with the lowest possible [order].</p>
  
  <p>E.g. The input table, valid_columns looks like this:</p>
  
  <pre><code>name    col_order
  -------------
  job     1   
  job     3   
  status  2   
  cust    2   
  county  1   
  state   1   
  region  1
  region  2
  region  3
  so      4
  </code></pre>
  
  <p>Desired Output:</p>
  
  <pre><code>name    col_order
  -------------
  job     1       
  status  2   
  cust    2   
  county  1   
  state   1   
  region  1
  so      4
  </code></pre>
  
  <p>I am trying to fix a bug and I can't figure out the SQL. Currently it uses a delete statment and a subquery. The query used at the moment looks like this:</p>
  
  <p>-- 3) Remove duplicated columns</p>
  
  <pre><code>DELETE 
  FROM valid_columns
  WHERE   NOT ( col_order = ( SELECT  TOP 1 col_order 
              FROM    valid_columns   firstValid
              WHERE   name = firstValid.name
              AND col_order = firstValid.col_order
              ORDER BY col_order ASC ))
  </code></pre>
  
  <p>However, this only returns the following, which is incorrect:</p>
  
  <pre><code>name    col_order
  -------------
  job     1
  county  1
  state   1
  region  1
  </code></pre>
  
  <p>Many thanks</p>