Question

Source:https://stackoverflow.com/questions/70512837/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 compared 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) is 3^rd and a_Honda (6500) is 5^th 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?

Answer

This is simple. You just append rank of the current SoldCar among the first 6 columns to the corresponding record. It is complicated to do this in SQL. The language needs the cross apply assisted by XQuery, or by OpenJson and the window function. The statement is lengthy and hard to read. An alternative is to export data out of database and handle it in Python or SPL. SPL, the open-source Java package, is easier to be integrated into a Java program and generate much simpler code. It gets this done with only three lines of code:

	A
1	=MSSQL.query("select   a_Toyota,a_Mazda,a_Nissan,a_Kia,a_Honda,a_Subaru,'a_'+SoldCar as SoldCar from   cars")
2	=A1.fname().m(:-2)
3	=A1.derive([${A2.concat@c()}].ranks@z()(A2.pselect(~==A1.~.#7)):CarOrder)

 

View SPL source code.