I have a table with two columns of integers. I would like to select data from it (in some standard way) and add a new column by rule: if number on left is bigger, new column is 1 if number on left is smaller, new column is -1 if numbers are equal, new column is 0 Example: 16 | 15 | 1 20 | 28 |-1 11 | 11 | 0 28 | 14 | 1 ...

Reputation: 2559

SQL SELECT : map columns to new column

I have a table with two columns of integers. I would like to select data from it (in some standard way) and add a new column by rule:

Example:

16 | 15 | 1
20 | 28 |-1
11 | 11 | 0
28 | 14 | 1
...

Upvotes: 1

Reputation: 69809

Sounds like you want the SIGN Function

SELECT Col1, Col2, SIGN(Col1 - Col2) AS Col3
FROM   T

Upvotes: 5

Reputation: 7517

SELECT X,Y,
    CASE WHEN X > Y THEN 1
         WHEN X < Y THEN -1
         ELSE 0 END AS "Z"
FROM table_name

Upvotes: 5