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 % operatorSELECT ID FROM YourTable WHERE (ID % 2) = 0 --displays evensSELECT 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] |
|
|
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) |
|
|
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] |
|
|
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 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-22 : 00:51:57
|
Sounds one of the Interview questions MadhivananFailing to plan is Planning to fail |
|
|
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 |
|
|
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."-- Evenwhere convert(varbinary(1),right(id,1)) in (0x30,0x32,0x34,0x36,0x38)-- Oddwhere convert(varbinary(1),right(id,1)) in (0x31,0x33,0x35,0x37,0x39) CODO ERGO SUM |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-22 : 07:09:50
|
"Hand optimised" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-22 : 08:03:46
|
MVJ, very different approach MadhivananFailing to plan is Planning to fail |
|
|
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 MadhivananFailing 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 - 2006-03-22 : 14:10:03
|
You might be able to get a higher id10t score using:SELECT * FROM myTable WHERE id LIKE '%[02468]' -- evenSELECT * FROM myTable WHERE id NOT LIKE '%[02468]' -- odd -PatP |
|
|
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]' -- evenSELECT * 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
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:-- Evenwhere convert(varbinary(1),right(id,1)) NOT in (0x31,0x33,0x35,0x37,0x39)-- Oddwhere convert(varbinary(1),right(id,1)) NOT in (0x30,0x32,0x34,0x36,0x38) |
|
|
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:-- Evenwhere convert(varbinary(1),right(id,1)) NOT in (0x31,0x33,0x35,0x37,0x39)-- Oddwhere 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 <> 1where -- Odd convert(tinyint,convert(varchar(1),convert(varbinary(1),right(ID,1)))) & 1 <> 0 CODO ERGO SUM |
|
|
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] |
|
|
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. :) |
|
|
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 |
|
|
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] |
|
|
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] |
|
|
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) -- OddSELECT * FROM myTable WHERE 'e' = SubString('This is just a blithering pile of stuff to filleoeoeoeoeo', Ascii(Reverse(id)), 1) -- Even -PatP |
|
|
Next Page
|