March 2, 2013 at 7:03 am
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?
March 2, 2013 at 8:57 am
Unfortunately I'm struggling to understand your problem scenario. Can you share the table schema and some sample data please?
March 2, 2013 at 11:11 am
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
Change is inevitable... Change for the better is not.
March 2, 2013 at 11:37 am
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
March 2, 2013 at 11:43 am
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.
March 2, 2013 at 12:04 pm
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
March 2, 2013 at 12:07 pm
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
March 2, 2013 at 10:10 pm
The database engine is oracle!!! and I am trying to display odd number of records in table!
March 2, 2013 at 10:12 pm
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!!
March 2, 2013 at 11:47 pm
You would probably get better answers if you asked this on an Oracle forum. This is a Microsoft SQL Server forum.
March 3, 2013 at 1:37 am
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
March 25, 2016 at 2:41 am
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).
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy