sql server - Removing duplicate rows (based on values from multiple columns) from SQL table -

i have following sql table:

ar_customer_shipto

+--------------+------------+-------------------+------------+ | 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 | +--------------+------------+-------------------+------------+ 

(ardivisionno, customerno,shiptocode) form primary key table.

if notice first 3 rows belong same customer (test customer), has different shiptocodes: 1, 2 , 3. similar case second customer (aracode customer). each of normal customer , normal customer-2 has 1 record single shiptocode.

now, result querying on table, have 1 record per customer. so, customer, there more 1 records, keep record highest value shiptocode.

i tried various things:

(1) can list of customers 1 record in table.

(2) following query, able list of customers, have more 1 record in table.

[query-1]

select ardivisionno, customerno ar_customer_shipto  group ardivisionno, customerno having count(*) > 1; 

(3) now, in order select proper shiptocode each record returned above query, not able figure out, how iterate through records returned above query.

if like:

[query-2]

select top 1 ardivisionno, customerno, customername, shiptocode   ar_customer_shipto  ardivisionno = '00' , customerno = '1234567' order shiptocode desc 

then can appropriate record (00-1234567-test customer). hence, if can use results query-1 in above query (query-2), can desired single records customers more 1 record. can combined results point (1) achieve desired end result.

again, can easier approach following. please let me know how can this.

[note: have using sql queries only. cannot use stored procedures, going execute thing using 'scribe insight', allows me write queries.]

sample sql fiddle

1) use cte max ship code value record based on ardivisionno, customerno each customers

with cte (   select*,       row_number() over(partition ardivisionno, customerno order shiptocode desc) [rn]   t ) select * cte [rn] = 1 

2) delete record use delete query instead of select , change clause rn > 1. sample sql fiddle

with cte (   select*,       row_number() over(partition ardivisionno, customerno order shiptocode desc) [rn]   t ) delete cte [rn] > 1;  select * t;