My SQL Server table looks like this
ID a_Toyota a_Mazda a_Nissan a_Kia a_Honda a_Subaru SoldCar CarOrder
1 8000 7000 6200 8500 6500 7000 Mazda NULL
2 4000 5000 4500 3500 3500 5000 Mazda NULL
3 5400 5000 4500 5500 5500 4600 Mazda NULL
4 5600 6300 7500 8200 6500 7300 Mazda NULL
5 8500 7400 7400 6500 9500 9000 Mazda NULL
6 9900 8000 9900 7300 8100 8000 Mazda NULL
I want to Update CarOrder field, so it has the order of price of the sold car compare to other car prices.
So for ID 1 car prices ordered as a_Kia (8500) is 1st and a_Toyota (8000) is 2nd and a_Mazda & a_Subaru (7000) are 3rd and a_Honda (6500) is 5th and a_Nissan (6200) is 6th and the sold car was Mazda which is 3rd so the table should be as follow
ID a_Toyota a_Mazda a_Nissan a_Kia a_Honda a_Subaru SoldCar CarOrder
1 8000 7000 6200 8500 6500 7000 Mazda 3
2 4000 5000 4500 3500 3500 5000 Subaru 1
3 5400 5000 4500 5500 5500 4600 Toyota 3
4 5600 6300 7500 8200 6500 7300 Honda 4
5 8500 7400 7400 6500 9500 9000 Honda 1
6 9900 8000 9900 7300 8100 8000 Honda 3
I can find the order with a large CASE statement
UPDATE mytable
SET CarOrder =
CASE WHEN SoldCar = 'Toyota' AND a_Toyota>=a_Mazda AND a_Toyota>=a_Nissan AND ... AND a_Toyota>=a_Subaru THEN 1
CASE WHEN SoldCar = 'Toyota' AND a_Toyota<a_Mazda AND a_Toyota>=a_Nissan AND ... AND a_Toyota>=a_Subaru THEN 2
CASE WHEN SoldCar = 'Toyota' AND a_Toyota>=a_Mazda AND a_Toyota<a_Nissan AND ... AND a_Toyota>=a_Subaru THEN 2
.
.
.
CASE WHEN SoldCar = 'Toyota' AND a_Toyota>=a_Mazda AND a_Toyota>=a_Nissan AND ... AND a_Toyota<a_Subaru THEN 2
.
.
.
CASE WHEN SoldCar = 'Toyota' AND a_Toyota<a_Mazda AND a_Toyota<a_Nissan AND ... AND a_Toyota>=a_Subaru THEN 3
..
..
..
but this is going to be a huge case statement.
I wonder if someone has an easier way to do that?