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

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?

CodePudding user response：

Assuming a table structure similar to the following:

CREATE TABLE tempdb..cars
(
    ID INT NOT NULL,
    a_Toyota INT NOT NULL,
    a_Mazda INT NOT NULL,
    a_Nissan INT NOT NULL,
    a_Kia INT NOT NULL,
    a_Honda INT NOT NULL,
    a_Subaru INT NOT NULL,
    SoldCar VARCHAR(100) NOT NULL,
    CarOrder INT NULL
);

One approach would be to leverage an APPLY operator. Something like the following should give you a resultset from a table structure like above, assuming the use of CROSS APPLY and a non-dense RANK (vs a DENSE RANK) along with descending order for determining your ordering:

SELECT  c.ID, c.SoldCar, o.ord AS CarOrder
FROM    tempdb..cars c
CROSS APPLY
        (
        SELECT  t.ord
        FROM    (
                SELECT  r.car, RANK() OVER (ORDER BY r.qty DESC) AS ord
                FROM    (
                        SELECT  c.a_Toyota AS qty, 'Toyota' AS car
                        UNION ALL
                        SELECT  c.a_Mazda AS qty, 'Mazda' AS car
                        UNION ALL
                        SELECT  c.a_Nissan AS qty, 'Nissan' AS car
                        UNION ALL
                        SELECT  c.a_Kia AS qty, 'Kia' AS car
                        UNION ALL
                        SELECT  c.a_Honda AS qty, 'Honda' AS car
                        UNION ALL
                        SELECT  c.a_Subaru AS qty, 'Subaru' AS car
                        ) r
                ) t
        WHERE   t.car = c.SoldCar
        ) o

CodePudding user response：

The table structure needs pivoting into something it should be from the outset that would facilitate easily determining the correct order and catering for any number of brands.

You can do this using a cross-apply and row_number to match each value with it's ordinal position, then join the results back to the original table to update:

with cars as (
    select Id, Min(c.CarOrder) CarOrder
    from t
    cross apply (
        select case soldcar
            when 'Toyota' then a_Toyota
            when 'Mazda'  then a_Mazda
            when 'Nissan' then a_Nissan
            when 'Honda'  then a_Honda
            when 'Subaru' then a_Subaru
        end
    )s(SoldValue)
    cross apply (
        select row_number() over (order by v desc) CarOrder, v
        from (
            values(a_toyota),(a_mazda),(a_nissan),(a_kia),(a_honda),(a_subaru)
        )v(v)
    )c
    where SoldValue=v
    group by Id
)
update t
set t.CarOrder = c.CarOrder
from cars c join t on t.Id=C.Id

See Demo Fiddle