Fetch Odd or even records - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Fetch Odd or even records

Author

Topic

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2007-05-21 : 07:20:13

How can I fetch only Odd or only even records from a table.The odd or even is depends up on Id (Its a primary key/ Integer).

Is there any query

Please help me!

Shaji

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-21 : 07:35:34

EVEN


select *
from   table
where  id % 2 = 0

ODD


select *
from   table
where  id % 2 = 1

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2007-05-21 : 09:23:02

Thanks khtan,

But when I used this query in sql server2000, its showing an error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Operand data type numeric is invalid for modulo operator.

But Its working in server2005!

This is query I wrote;
SELECT * FROM C1_Messages WHERE (Status = 1) AND
(Id IN (SELECT Id FROM C1_Messages AS C1_Messages_1 WHERE (Id % 2 = 1)))ORDER BY Id

Could you explain why?

quote:
Originally posted by khtan

EVEN

select *
from table
where id % 2 = 0

ODD

select *
from table
where id % 2 = 1

KH

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-21 : 09:26:31

sounds like your column is not int, but numeric. 2005 must be implicitly casting to int. I am surprised it works though.

try this:

select *
from table
where convert(int,id) % 2 = 1

www.elsasoft.org

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-21 : 09:28:11

in SQL 2000 you will get error when you use the modular operator on numeric data type. This is fine in SQL 2005.

quote:
Its a primary key/ Integer

But you said this is an Integer ?

convert to int before applying the modular operation.

convert(int, id) % 2

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-21 : 09:31:50

quote:
Originally posted by jezemine

sounds like your column is not int, but numeric. 2005 must be implicitly casting to int. I am surprised it works though.

try this:

select *
from table
where convert(int,id) % 2 = 1

www.elsasoft.org

Actually No. I think just 2005 is able to use modular operator on numeric data type, even for numeric with precision & scale.

This is on 2005


declare @i numeric(10,2)

select @i = 10.2

select @i % 2
/* RESULT
.20
*/

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-21 : 11:31:56

ah, interesting. good point.

www.elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-21 : 11:40:42

<<
The odd or even is depends up on Id (Its a primary key/ Integer).
>>

Then you wont get this error

quote:

[Microsoft][ODBC SQL Server Driver][SQL Server]Operand data type numeric is invalid for modulo operator.

Post table structure

Madhivanan

Failing to plan is Planning to fail

sahasahp
Starting Member

1 Post

Posted - 2008-08-16 : 18:02:22

select Name,Salary from (select Name, salary,mod(rownum,2) odd from emp) where odd=1
select Name,Salary from (select Name, salary,mod(rownum,2) even from emp) where even=0

Is it a good query? Can you kidly review with your expertise?

Alternatively:
ODD
select * from emp where (rowid,1) IN (select rowid, MOD(rownum,2) from emp);
EVEN
select * from emp where (rowid,0) IN (select rowid, MOD(rownum,2) from emp);

for nth row to be fetched replace 2 by n. suppose n=10

select * from emp where (rowid,0) IN (select rowid, MOD(rownum,10) from emp);

Is it also a good query?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-08-16 : 23:21:23

No. It is a horrible query. They are all horrible.
They are needlessly complicated. They use superfluous nested subqueries. And, finally, they are thee months late for a thread which was resolved back in May.
If you want to see the best way to write these queries, just examine khtan's solution. Simplicity=Elegance.

Boycott Beijing Olympics 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources