SQL SELECT : map columns to new column

Question

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
...

score 5 · Answer 1 · answered May 02 '12 at 20:49

5

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

answered May 02 '12 at 20:49

Chad

Is this "CASE" in standard? I was thinking about joining with (SELECT -1/0/1) ON (one of 3 conditions) and grouping it somehow... – Ivan Kuckir May 02 '12 at 21:34
Yes, it is standard SQL. See the answers to [this question](http://stackoverflow.com/questions/4622/sql-case-statement-syntax). – Chad May 02 '12 at 21:40

score 5 · Answer 2 · answered May 02 '12 at 20:50

5

Sounds like you want the SIGN Function

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

answered May 02 '12 at 20:50

GarethD

2 Answers2