updated on more

Is there a faster way to find the order of a COLUMN?

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?

Recommended Articles