I have two columns Speed_A and Speed_B. Now I will compare both columns and will select the higher one in a new table. Something like this:
SELECT ... CASE WHEN a.Speed_A > a.Speed_B THEN a.Speed_A WHEN a.Speed_A < a.Speed_B THEN a.Speed_B ELSE 0 --unknown END as SpeedLimit,
How does it work?
Advertisement
Answer
Assuming your speeds are numerically typed you might get this as easy as:
SELECT CASE WHEN a.Speed_A>a.Speed_B THEN a.Speed_A ELSE a.Speed_B END AS SpeedLimit [...FROM SomeWhere...]
Your ELSE
will only occur, when A and B are equal, In this case it doesn’t matter, which one you return.
UPDATE
columnĀ“s data type is integer. And yes there are nulls
If A is null
return B, If b is null
return A (works for A and B is null
implicitly). Otherwise both are not null and therefore the statement above should be perfect
SELECT CASE WHEN a.Speed_A IS NULL THEN a.Speed_B ELSE CASE WHEN a.Speed_B IS NULL THEN a.Speed_A ELSE CASE WHEN a.Speed_A>a.Speed_B THEN a.Speed_A ELSE a.Speed_B END END END AS SpeedLimit FROM Dummy [...FROM SomeWhere...]