Click here to Skip to main content
14,732,665 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Comments
Maciej Los 19-May-20 13:45pm
   
Your select statement is invalid, so you can't get above result.

1 solution

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
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900