Skip to content
Advertisement

How to compare two columns in the CASE statement

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...]
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement