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
 General SQL Server Forums
 New to SQL Server Programming
 even and odd records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ghostrider
Starting Member

India
11 Posts

Posted - 03/02/2013 :  09:04:18  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 03/02/2013 :  12:04:14  Show Profile  Reply with Quote
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

938 Posts

Posted - 03/02/2013 :  18:15:19  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 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.08 seconds. Powered By: Snitz Forums 2000