SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Even and Odd
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

oahu9872
Posting Yak Master

USA
112 Posts

Posted - 03/21/2006 :  13:58:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 03/21/2006 :  14:08:22  Show Profile  Visit jhermiz's Homepage  Reply with Quote
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 -- http://www.web-impulse.com

RS Blog -- http://weblogs.sqlteam.com/jhermiz

Edited by - jhermiz on 03/24/2006 13:53:11
Go to Top of Page

Srinika
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 03/21/2006 :  15:14:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 03/21/2006 :  15:17:28  Show Profile  Visit jhermiz's Homepage  Reply with Quote
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 -- http://www.web-impulse.com

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 03/21/2006 :  17:23:23  Show Profile  Reply with Quote
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

India
22772 Posts

Posted - 03/22/2006 :  00:51:57  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Sounds one of the Interview questions

Madhivanan

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

Kristen
Test

United Kingdom
22431 Posts

Posted - 03/22/2006 :  01:45:39  Show Profile  Reply with Quote
"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)

USA
7020 Posts

Posted - 03/22/2006 :  07:02:17  Show Profile  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 03/22/2006 :  07:09:50  Show Profile  Reply with Quote
"Hand optimised"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 03/22/2006 :  08:03:46  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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)

USA
7020 Posts

Posted - 03/22/2006 :  10:48:08  Show Profile  Reply with Quote
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 - 03/22/2006 :  14:10:03  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 03/22/2006 :  17:55:42  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 03/23/2006 :  09:53:36  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 03/23/2006 :  10:29:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 03/23/2006 :  10:33:11  Show Profile  Visit jhermiz's Homepage  Reply with Quote
This is going a bit off topic dont you think :)



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

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

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 03/23/2006 :  11:29:14  Show Profile  Visit mr_mist's Homepage  Reply with Quote
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)

USA
7020 Posts

Posted - 03/23/2006 :  11:31:32  Show Profile  Reply with Quote
quote:
Originally posted by jhermiz

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



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

RS Blog -- http://weblogs.sqlteam.com/jhermiz



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



CODO ERGO SUM
Go to Top of Page

jhermiz
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 03/23/2006 :  11:31:39  Show Profile  Visit jhermiz's Homepage  Reply with Quote
lol


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

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

jhermiz
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 03/23/2006 :  11:32:33  Show Profile  Visit jhermiz's Homepage  Reply with Quote
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 -- http://www.web-impulse.com

RS Blog -- http://weblogs.sqlteam.com/jhermiz



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 -- http://www.web-impulse.com

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

Pat Phelan
Posting Yak Master

187 Posts

Posted - 03/23/2006 :  12:02:17  Show Profile  Reply with Quote
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
Page: of 3 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000