'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?
Solution 1:[1]
One more method based on XQuery.
For the row where ID=2, there is a tie between Subary and Mazda. They both have value of 5000.
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (
ID INT IDENTITY(1,1) PRIMARY KEY,
a_Toyota INT,
a_Mazda INT,
a_Nissan INT,
a_Kia INT,
a_Honda INT,
a_Subaru INT,
SoldCar VARCHAR(20)
);
INSERT INTO @tbl
(
a_Toyota,
a_Mazda,
a_Nissan,
a_Kia,
a_Honda,
a_Subaru,
SoldCar
) VALUES
(8000, 7000, 6200, 8500, 6500, 7000, 'Mazda'),
(4000, 5000, 4500, 3500, 3500, 5000, 'Subaru'),
(5400, 5000, 4500, 5500, 5500, 4600, 'Toyota'),
(5600, 6300, 7500, 8200, 6500, 7300, 'Honda'),
(8500, 7400, 7400, 6500, 9500, 9000, 'Honda'),
(9900, 8000, 9900, 7300, 8100, 8000, 'Honda');
-- DDL and sample data population, end
SELECT t.*, CarOrder
FROM @tbl AS t
CROSS APPLY (SELECT a_Toyota, a_Mazda, a_Nissan,
a_Kia, a_Honda, a_Subaru, SoldCar
FOR XML PATH(''), TYPE, ROOT('root')) AS t1(c)
CROSS APPLY (SELECT c.query('<root>
{
for $r in /root/*
order by data($r) descending
return <r>
<make>{local-name($r)}</make>
<salePrice>{data($r)}</salePrice>
</r>
}
</root>').query('
let $soldcar := sql:column("SoldCar")
for $r in /root/r[contains((make/text())[1], $soldcar)]
let $pos := count(root/*[. << $r])
return $pos').value('.','INT')
) AS t2(CarOrder);
Output
+----+----------+---------+----------+-------+---------+----------+---------+----------+
| 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 | 2 |
| 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 |
+----+----------+---------+----------+-------+---------+----------+---------+----------+
Solution 2:[2]
Here is an option where you don't have to enumerate the columns to unpivot.
This also assumes the column names have the prefix of a_
Example or dbFiddle
with cte as (
Select *
From YourTable A
Cross Apply (
Select *
,rn=row_number() over (order by convert(decimal(12,2),value) desc)
From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper ))
Where [Key] not in ('ID','SoldCar','CarOrder')
) B
Where [key] ='a_'+SoldCar collate SQL_Latin1_General_CP1_CI_AS
)
Update cte set CarOrder = RN
The Updated Table
Solution 3:[3]
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
Solution 4:[4]
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 its ordinal position, using an updatable CTE to update the base table.
with cars as (
select * 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 rank() over (order by v desc) co, v
from (values(a_toyota),(a_mazda),(a_nissan),(a_kia),(a_honda),(a_subaru))v(v)
)c
where SoldValue=v
)
update cars set carOrder=co
See Demo Fiddle
Solution 5:[5]
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) |
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Yitzhak Khabinsky |
| Solution 2 | |
| Solution 3 | boydc7 |
| Solution 4 | |
| Solution 5 |