Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Even and Odd

Author  Topic 

oahu9872
Posting Yak Master

112 Posts

Posted - 2006-03-21 : 13:58:02
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?

jhermiz

3564 Posts

Posted - 2006-03-21 : 14:08:22
quote:
Originally posted by oahu9872

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

Good luck


Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]
Go to Top of Page

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-21 : 15:14:36
u may be better off if u do it in ur front end
(using same or similar logic as jhermiz says)
Go to Top of Page

jhermiz

3564 Posts

Posted - 2006-03-21 : 15:17:28
quote:
Originally posted by Srinika

u may be better off if u do it in ur front end
(using same or similar logic as jhermiz says)



I would say this is a bad suggestion. Pulling back all the data and then displaying or filtering records in the front end is not what you want to do.

You can modify that select with a bit or int to specify whether you want odd or even.




Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-21 : 17:23:23
quote:
Originally posted by oahu9872

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?


OK, I have to ask. Why do you need to do this?





CODO ERGO SUM
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-22 : 00:51:57
Sounds one of the Interview questions

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-03-22 : 01:45:39
"Sounds one of the Interview questions"

hehehehe ... so the company doing the interview has got an interview question for a method that is regarded as bad practice on SQL Team?

Here's my answer then:

"Sorry chaps, if that is how you solve that sort of problem here I won't be able to work for you"!

Kristen
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-22 : 07:02:17
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)





CODO ERGO SUM
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-03-22 : 07:09:50
"Hand optimised"
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-22 : 08:03:46
MVJ, very different approach

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-22 : 10:48:08
quote:
Originally posted by madhivanan

MVJ, very different approach

Madhivanan

Failing to plan is Planning to fail


I have a lot of pride in my ability to develop technically sophisticated and totally impractical solutions to stupid requirements.

SQLTeam provides me with endless opportunities to demonstrate that ability. That's why I love this place.




CODO ERGO SUM
Go to Top of Page

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2006-03-22 : 14:10:03
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-22 : 17:55:42
quote:
Originally posted by Pat Phelan

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.



CODO ERGO SUM
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-23 : 09:53:36
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)
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-23 : 10:29:56
quote:
Originally posted by blindman

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





CODO ERGO SUM
Go to Top of Page

jhermiz

3564 Posts

Posted - 2006-03-23 : 10:33:11
This is going a bit off topic dont you think :)



Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]
Go to Top of Page

mr_mist
Grunnio

1870 Posts

Posted - 2006-03-23 : 11:29:14
quote:
Originally posted by jhermiz

This is going a bit off topic dont you think :)



Oddly they are still providing solutions to the original question. ...

-------
Moo. :)
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-23 : 11:31:32
quote:
Originally posted by jhermiz

This is going a bit off topic dont you think :)



Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]



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



CODO ERGO SUM
Go to Top of Page

jhermiz

3564 Posts

Posted - 2006-03-23 : 11:31:39
lol


Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]
Go to Top of Page

jhermiz

3564 Posts

Posted - 2006-03-23 : 11:32:33
quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by jhermiz

This is going a bit off topic dont you think :)



Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]



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 ;)


Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]
Go to Top of Page

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2006-03-23 : 12:02:17
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
-PatP
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -