title: Removing duplicate rows (based on values from multiple columns) from SQL table
body: <p>I have following SQL table:</p>
  
  <p><strong>AR_Customer_ShipTo</strong></p>
  
  <pre><code>+--------------+------------+-------------------+------------+
  | ARDivisionNo | CustomerNo |   CustomerName    | ShipToCode |
  +--------------+------------+-------------------+------------+
  |           00 | 1234567    | Test Customer     |          1 |
  |           00 | 1234567    | Test Customer     |          2 |
  |           00 | 1234567    | Test Customer     |          3 |
  |           00 | ARACODE    | ARACODE Customer  |          1 |
  |           00 | ARACODE    | ARACODE Customer  |          2 |
  |           01 | CBE1EX     | Normal Customer   |          1 |
  |           02 | ZOCDOC     | Normal Customer-2 |          1 |
  +--------------+------------+-------------------+------------+
  </code></pre>
  
  <p><code>(ARDivisionNo, CustomerNo,ShipToCode)</code> form a primary key for this table. </p>
  
  <p>If you notice first 3 rows belong to same customer (Test Customer), who has different ShipToCodes: 1, 2 and 3. Similar is the case with second customer (ARACODE Customer). Each of Normal Customer and Normal Customer-2 has only 1 record with a single <code>ShipToCode</code>. </p>
  
  <p>Now, I would like to get result querying on this table, where I will have only 1 record per customer. So, for any customer, where there are more than 1 records, I would like to keep the record with highest value for <code>ShipToCode</code>.</p>
  
  <p>I tried various things: </p>
  
  <p>(1) I can easily get the list of customers with only one record in table.</p>
  
  <p>(2) With following query, I am able to get the list of all the customers, who have more than one record in the table.</p>
  
  <p><strong>[Query-1]</strong></p>
  
  <pre><code>SELECT ARDivisionNo, CustomerNo
  FROM AR_Customer_ShipTo 
  GROUP BY ARDivisionNo, CustomerNo
  HAVING COUNT(*) &gt; 1;
  </code></pre>
  
  <p>(3) Now, in order to select proper <code>ShipToCode</code> for each record returned by above query, I am not able to figure out, how to iterate through all the records returned by above query. </p>
  
  <p>If I do something like:</p>
  
  <p><strong>[Query-2]</strong></p>
  
  <pre><code>SELECT TOP 1 ARDivisionNo, CustomerNo, CustomerName, ShipToCode  
  FROM AR_Customer_ShipTo 
  WHERE ARDivisionNo = '00' and CustomerNo = '1234567'
  ORDER BY ShipToCode DESC
  </code></pre>
  
  <p>Then I can get the appropriate record for (00-1234567-Test Customer). Hence, if I can use all the results from query-1 in the above query (query-2), then I can get the desired single records for customers with more than one record. This can be combined with results from point (1) to achieve the desired end result.</p>
  
  <p>Again, this can be easier than approach I am following. Please let me know how can I do this.</p>
  
  <p>[Note: I have to do this using SQL queries only. I cannot use stored procedures, as I am going to execute this thing finally using 'Scribe Insight', which only allows me to write queries.]</p>