14,732,665 members
See more: , +
I have around 10,000 customer data containing customer number, account number & account rank (1,2,3,4). several customers have multiple account numbers. i want to update highest rank for unique customer number. i.e account rank must be same for single customer.

Let me know the query to get this kind of data & update the same.

example.

```Customer, account, rank
a,1,0
b,2,1
a,3,3
a,4,2
c,5,0```

so in above case i require to update rank=3 for account number 1 & 4.

What I have tried:

`select customer,max(rank) where count(customer)>1 group by customer`
Posted
Updated 19-May-20 8:58am
v2
Maciej Los 19-May-20 13:45pm

Your select statement is invalid, so you can't get above result.

## Solution 1

Try this:
```DECLARE @customer TABLE(Customer VARCHAR(50), account int, [rank] int)

INSERT INTO @customer (Customer, account, [rank])
VALUES('a',1,0),
('b',2,1),
('a',3,3),
('a',4,2),
('c',5,0)

UPDATE t1 SET [rank] =  t2.MaxRank
FROM @Customer t1 INNER JOIN
(
SELECT Customer, MAX([rank]) MaxRank
FROM @customer
GROUP BY Customer
) t2
ON t1.Customer  = t2.Customer```