even and odd records

Question

even and odd records

mehta.saurabhj

SSChasing Mays

Points: 638
More actions
March 2, 2013 at 7:03 am

#290467

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?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

John.Sansom SSCertifiable Points: 7335 More actions · Answer 1

Unfortunately I'm struggling to understand your problem scenario. Can you share the table schema and some sample data please?

John Sansom (@sqlBrit) | www.johnsansom.com

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

mehta.saurabhj (3/2/2013)
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?

Hard to tell. Please post your MOD function ad the CREATE TABLE statements for the 2 tables involved. It may help. If you want some really good help, take a little time and read the article at the first link in my signature below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mehta.saurabhj SSChasing Mays Points: 638 More actions · Answer 3

create table student

(name varchar(20),age int);

insert into student values('ram',12);

insert into student values('shyam',13);

insert into student values('balu',14);

By processing the 2nd query the output I get is

name age

ram 12

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

mehta.saurabhj (3/2/2013)
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?

Dare I say that this looks like ORACLE not MS SQL Server? Unless I have missed something MS SQL Server doesn't allow a multi-column IN clause.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Lynn Pettis (3/2/2013)
mehta.saurabhj (3/2/2013)
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?
Dare I say that this looks like ORACLE not MS SQL Server? Unless I have missed something MS SQL Server doesn't allow a multi-column IN clause.

It doesn't, and SQL doesn't have a mod function either (and it's not a udf, as that would have to be SchemaName.mod). To calculate the modulus in SQL, you would use the % operator.

What database engine is this? Oracle? MySQL? PostgreSQL? Something else?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

For SQL Server, this works as expected (as long as good performance isn't expected).

CREATE TABLE student ( name VARCHAR(20), age INT );

INSERT INTO student

VALUES ( 'ram', 12 );

INSERT INTO student

VALUES ( 'shyam', 13 );

INSERT INTO student

VALUES ( 'balu', 14 );

INSERT INTO student

VALUES ( 'Tony', 15 );

-- even ages

SELECT *

FROM student

WHERE age % 2 = 0

-- odd ages

SELECT *

FROM student

WHERE age % 2 = 1

DROP TABLE student

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mehta.saurabhj SSChasing Mays Points: 638 More actions · Answer 7

The database engine is oracle!!! and I am trying to display odd number of records in table!

mehta.saurabhj SSChasing Mays Points: 638 More actions · Answer 8

GilaMonster (3/2/2013)
For SQL Server, this works as expected (as long as good performance isn't expected).
CREATE TABLE student ( name VARCHAR(20), age INT );
INSERT INTO student
VALUES ( 'ram', 12 );
INSERT INTO student
VALUES ( 'shyam', 13 );
INSERT INTO student
VALUES ( 'balu', 14 );
INSERT INTO student
VALUES ( 'Tony', 15 );
-- even ages
SELECT *
FROM student
WHERE age % 2 = 0
-- odd ages
SELECT *
FROM student
WHERE age % 2 = 1
DROP TABLE student

I am trying to display odd number of records and not 'ODD AGES'!!please help to clear the doubt I asked earlier!!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

You would probably get better answers if you asked this on an Oracle forum. This is a Microsoft SQL Server forum.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

mehta.saurabhj (3/2/2013)
GilaMonster (3/2/2013)
For SQL Server, this works as expected (as long as good performance isn't expected).
CREATE TABLE student ( name VARCHAR(20), age INT );
INSERT INTO student
VALUES ( 'ram', 12 );
INSERT INTO student
VALUES ( 'shyam', 13 );
INSERT INTO student
VALUES ( 'balu', 14 );
INSERT INTO student
VALUES ( 'Tony', 15 );
-- even ages
SELECT *
FROM student
WHERE age % 2 = 0
-- odd ages
SELECT *
FROM student
WHERE age % 2 = 1
DROP TABLE student
I am trying to display odd number of records and not 'ODD AGES'!!please help to clear the doubt I asked earlier!!

Define 'Record number'. Record number as defined by what? There's no order of rows in a database table, hence no 'odd' or 'even' rows. You can say the odd rows when the resultset is ordered by a particular column, but that's all.

I can give you a SQL Server solution, as this is a SQL Server forum. If you want Oracle, maybe ask on an Oracle forum?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ben.brugman SSChampion Points: 13369 More actions · Answer 11

mehta.saurabhj (3/2/2013)
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?

Bit late in the game.:cool:

For the second line of code.

Isn't the second row an even row by definition ?

So any row to be considered as a second row is rejected.

Ben

Yes I am a bit late. (Sorry).