CODEWITHSUNDEEP
sqlsql-server
Gopal
Gopal

Reputation: 11982

How to get a non matching record in single row

Table1

ID

001
002
001
001
001

...

I want to check the id from table1 where id should be even. If id is different then i need to return 2 else 1

How to write a query for this?

Upvotes: 0

Views: 42

Answers (3)

David Faber
David Faber

Reputation: 12485

If I understand the question correctly, a CASE statement is not necessary here. I'm assuming you want to return 2 when ID is even, and 1 when ID is odd? As long as there aren't any non-digit characters in the values of the ID column, you can do the following:

SELECT [ID], 2 - CAST([ID] AS int) % 2
  FROM Table1

If you want to return 2 when ID is odd, and 1 when it is even (sorry, that wasn't clear from the question), then you can do this:

SELECT [ID], CAST([ID] AS int) % 2 + 1
  FROM Table1

Upvotes: 0

potashin
potashin

Reputation: 44581

For IDs

SELECT (CASE WHEN [ID]%2 = 1 THEN 1 ELSE 2 END) 
FROM [table]

For ID COUNT :

 SELECT (CASE WHEN COUNT([ID])%2 = 1 THEN 1 ELSE 2 END)
 FROM [table] 
 GROUP BY [ID]

Upvotes: 2

Ajay2707
Ajay2707

Reputation: 5798

Please check this.

 declare @t table (id varchar(50))

insert into @t values('001'),('001'),('002'),('002'),('001'),('002'),('002')

SELECT 
    CASE WHEN cast( [ID] as int) %2 = 1 THEN 1 ELSE 2 END oddOrEven
FROM @t

--for counting
;with cte as
(
    SELECT [ID]%2 value,
        CASE cast( [ID] as int) %2 when 1 THEN count(1) else count(2) END  oddCount         
    FROM @t
        group by id
)
select * from cte

Upvotes: 0

Related Questions