I have a table where the first column is an auto-numbered ID field. Can I do a select statement that picks out only the IDs that are even numbers or odd numbers?

I have a table where the first column is an auto-numbered ID field. Can I do a select statement that picks out only the IDs that are even numbers or odd numbers?

Even numbers are divisible by 2, odds arent, so you can make use of the % operator

SELECT ID FROM YourTable WHERE (ID % 2) = 0 --displays evens SELECT ID FROM YourTable WHERE (ID % 2) <> 0 --displays odds

I have a table where the first column is an auto-numbered ID field. Can I do a select statement that picks out only the IDs that are even numbers or odd numbers?

This would be much more impressive at a job interview. Probably help them to make a decision right then.

"Yes, that's a very interesting approach to solving this problem."

-- Even
where convert(varbinary(1),right(id,1)) in (0x30,0x32,0x34,0x36,0x38)
-- Odd
where convert(varbinary(1),right(id,1)) in (0x31,0x33,0x35,0x37,0x39)

You might be able to get a higher id10t score using:

SELECT * FROM myTable WHERE id LIKE '%[02468]' -- even
SELECT * FROM myTable WHERE id NOT LIKE '%[02468]' -- odd

-PatP

I must disagree. Your solution, while it does have an idiot factor, still has a certain elegance and simplicity. I feel my solution lacks both elegance and simplicity while making full use of unnecessary and confusing technical features.

Well, I realize this is the sporting equivalent to adding an pluralizing the last word your opponent made in a game of scrabble, but is seems to me the obfuscatory factor could easily be increased this way:

-- Even
where convert(varbinary(1),right(id,1)) NOT in (0x31,0x33,0x35,0x37,0x39)
-- Odd
where convert(varbinary(1),right(id,1)) NOT in (0x30,0x32,0x34,0x36,0x38)

Well, I realize this is the sporting equivalent to adding an pluralizing the last word your opponent made in a game of scrabble, but is seems to me the obfuscatory factor could easily be increased this way:

-- Even
where convert(varbinary(1),right(id,1)) NOT in (0x31,0x33,0x35,0x37,0x39)
-- Odd
where convert(varbinary(1),right(id,1)) NOT in (0x30,0x32,0x34,0x36,0x38)

I see your point, so I am forced into a serious escalation.

where
-- Even
convert(tinyint,convert(varchar(1),convert(varbinary(1),right(ID,1)))) & 1 <> 1
where
-- Odd
convert(tinyint,convert(varchar(1),convert(varbinary(1),right(ID,1)))) & 1 <> 0

I resent that. All the code I posted meets the requirements of the original question.

CODO ERGO SUM

True but I think the requirements were met replies ago...Im still wondering why the original poster wanted to do this. Maybe its some sort of client end feature ;)

quote:Originally posted by Michael Valentine Jones

I must disagree. Your solution, while it does have an idiot factor, still has a certain elegance and simplicity. I feel my solution lacks both elegance and simplicity while making full use of unnecessary and confusing technical features.

Ok, increasing the id10t value yet again, and meeting your additional goals of "interesting use of features", I'll extend my original suggestion with:

SELECT *
FROM myTable
WHERE 'o' = SubString('This is just a blithering pile of stuff to filleoeoeoeoeo', Ascii(Reverse(id)), 1) -- Odd
SELECT *
FROM myTable
WHERE 'e' = SubString('This is just a blithering pile of stuff to filleoeoeoeoeo', Ascii(Reverse(id)), 1) -- Even