| Author |
 Topic  |
|
oahu9872
Posting Yak Master
USA
112 Posts |
 Posted - 03/21/2006 : 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
Flowing Fount of Yak Knowledge
USA
3564 Posts |
Posted - 03/21/2006 : 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 -- http://www.web-impulse.com
RS Blog -- http://weblogs.sqlteam.com/jhermiz |
Edited by - jhermiz on 03/24/2006 13:53:11 |
 |
|
|
Srinika
Flowing Fount of Yak Knowledge
Sri Lanka
1378 Posts |
Posted - 03/21/2006 : 15:14:36
|
u may be better off if u do it in ur front end
(using same or similar logic as jhermiz says) |
|
|
|
jhermiz
Flowing Fount of Yak Knowledge
USA
3564 Posts |
Posted - 03/21/2006 : 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 -- http://www.web-impulse.com
RS Blog -- http://weblogs.sqlteam.com/jhermiz |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
Posted - 03/21/2006 : 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 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 03/22/2006 : 00:51:57
|
Sounds one of the Interview questions 
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 03/22/2006 : 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 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
Posted - 03/22/2006 : 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 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 03/22/2006 : 07:09:50
|
| "Hand optimised" |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 03/22/2006 : 08:03:46
|
MVJ, very different approach
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
Posted - 03/22/2006 : 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 |
|
|
|
Pat Phelan
Posting Yak Master
187 Posts |
Posted - 03/22/2006 : 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
|
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
Posted - 03/22/2006 : 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 |
|
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 03/23/2006 : 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)
|
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
Posted - 03/23/2006 : 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 |
|
|
|
jhermiz
Flowing Fount of Yak Knowledge
USA
3564 Posts |
|
|
mr_mist
Grunnio
United Kingdom
1870 Posts |
Posted - 03/23/2006 : 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. :) |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
Posted - 03/23/2006 : 11:31:32
|
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 |
|
|
|
jhermiz
Flowing Fount of Yak Knowledge
USA
3564 Posts |
|
|
jhermiz
Flowing Fount of Yak Knowledge
USA
3564 Posts |
Posted - 03/23/2006 : 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 -- 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 |
|
|
|
Pat Phelan
Posting Yak Master
187 Posts |
Posted - 03/23/2006 : 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
|
|
|
|
Topic |
|
Even and Odd
