Even or odd in SQL

Question

This is table structure

id
1
2
3
4
5
6

I need result like this

id  even  odd
1   0     1
2   1     0
3   0     1
4   1     0
5   0     1
6   1     0

I tried

select id %2=0 then 1 else 0 end or id%2 <>0 then 1 else 0 odd 
from table

Solution 1

How about

select 
  id, 
  ~id & 1, 
  id & 1 
from t

Solution 2

Take a look at the CASE keyword. It works very similarly to what you're trying to do in your SELECT statement. In addition, if you want to select multiple columns, separate them with a comma. The OR keyword is used for combining logical conditions in your query, not for specifying multiple columns.

An example of how you could use CASE in your query would be as follows:

SELECT id,
       CASE WHEN id %2=0 THEN 1 ELSE 0 END AS Even,
       [column2]
FROM   [TableName]

Solution 3

The table structure is just Id?

you could try this!

 select *,
    case when id %2=0 then 1 else 0 end as even,
    case when id %2 <>0 then 1 else 0 end as odd
    from table

Solution 4

You have the right idea, but your syntax is a bit off. I'd use a CASE statement to create the even column, and then a calculate odd accordingly:

SELECT id, even, ABS(even - 1) AS odd
FROM   (SELECT id, CASE WHEN id % 2 = 0 THEN 1 ELSE 0 END AS even
        FROM   my_table)

View more solutions