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
 even and odd records

Author  Topic 

ghostrider
Starting Member

11 Posts

Posted - 2013-03-02 : 09:04:18
The sql code to find odd number of records is:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);

But if I do the foll code for odd nos:
select * from student where mod(rownum,2)=1;

Then it displays only the first row!Why is that so because I think the logic is true for odd numbers.Can someone please give solution to this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-02 : 12:04:14
this is syntactically not correct as per t-sql

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-03-02 : 18:15:19
I think this is Oracle. If so, rownum works on the outputted row so any construct with ROWNUM is almost certainly going to be wrong in the outer query unless it's where rownum < xxx
Try an inner query
SELECT * FROM
(SELECT STUDENT.*, ROWNUM r FROM STUDENT ORDER BY ZZZ) as X
X
WHERE mod(r,2)=1

Note the order by. Rows don't have an order unless you specify so none of the queries makes sense (other than a randon 50% sample) until you order your set.
Go to Top of Page
   

- Advertisement -