even and odd records

  • 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?

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

  • 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)

  • 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

  • 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.

  • 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
  • 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
  • The database engine is oracle!!! and I am trying to display odd number of records in table!

  • 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!!

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

  • 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
  • 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