SQL Query
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: SQL Query

  1. #1
    Join Date
    Jul 2001
    Posts
    334
    How I can get odd rows from the table.

    such as:

    1st row
    3rd row
    5th row
    7th row
    .
    .
    .
    .
    so on

    Any help using emp table.
    Thanks

  2. #2
    Join Date
    Feb 2001
    Posts
    17
    Hi,

    you could proceed as follows:

    1.) alter table scott.emp by adding a new column

    new_col | empno| job...
    --------------------------
    | 7566| Manager..............

    2.) update emp by inserting 1,2,...in this new column.

    new_col | empno| job...
    --------------------------
    1 | 7566| Manager..............
    2 | 7590| Clark..............
    ...

    3.) select * from scott.emp where MOD(new_col,2) != 0;

    ...this returns the rows where the numbers in new_col modulo 2 are not 0, i.e. the odd rows.


    Cheers,
    Dan1

  3. #3
    Join Date
    Aug 2001
    Posts
    267
    another way may be using rownum..

    select * from emp where rownum = 1


    put it in procedure use cursor etc.,

    row_num := 1

    row_num := row_num + 2



    select * from emp where rownum = :row_num;


    This may help
    Raghu

  4. #4
    Join Date
    Jul 2001
    Posts
    334
    I have to use only SQL not Pl/sql.

  5. #5
    Join Date
    Aug 2001
    Posts
    267
    I don't why can't you use pl/sql even from front end apps, they can call stored procs and they can results ..

    Here is another solution ..

    Create a view on table test having columns test1,test2 ..

    syntax:create view test1 as select rownum id,test1,test2 from test ;

    select * from test1 where mod(id,2) <> 0;


    Good luck


    Raghu
    Raghu

  6. #6
    Join Date
    Jul 2001
    Posts
    334
    Thanks for your solution. I will test it and let you know.

    Thanks.

  7. #7
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Hi,
    just another way using rownum:
    scott@oracle> select rownum rn, empno, ename from emp;

    RN EMPNO ENAME
    ---------- ---------- ----------
    1 7369 SMITH
    2 7499 ALLEN
    3 7521 WARD
    4 7566 JONES
    5 7654 MARTIN
    6 7698 BLAKE
    7 7782 CLARK
    8 7788 SCOTT
    9 7839 KING
    10 7844 TURNER
    11 7876 ADAMS
    12 7900 JAMES
    13 7902 FORD
    14 7934 MILLER

    14 rows selected.

    scott@oracle> select rn, empno, ename from
    2 ( select empno, ename, rownum rn from emp )
    3 where mod(rn,2)=1;

    RN EMPNO ENAME
    ---------- ---------- ----------
    1 7369 SMITH
    3 7521 WARD
    5 7654 MARTIN
    7 7782 CLARK
    9 7839 KING
    11 7876 ADAMS
    13 7902 FORD

    7 rows selected.


    Ales

  8. #8
    Join Date
    Jul 2001
    Posts
    334
    Cool!!! Excellent Ales,

    This is right solution, Yes it works. The report req. was really different to print odd records.

    Appreciate for this help.

    Thanks
    aph.

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You will get a different result set at different time when you use rownum in a SQL statement.

  10. #10
    Join Date
    Jul 2001
    Posts
    334
    tamilselvan:

    You mean depending on INSERT or DELETE

    Base on Ales solution if we insert one new row,
    So what will be the result? Please see below and correct me. Thanks


    scott@oracle> select rownum rn, empno, ename from emp;

    RN EMPNO ENAME
    ---------- ---------- ----------
    1 7369 SMITH
    2 7499 ALLEN
    3 7521 WARD
    4 7566 JONES
    5 7654 MARTIN
    6 7698 BLAKE
    7 7782 CLARK
    8 7788 SCOTT
    9 7839 KING
    10 7844 TURNER
    11 7876 ADAMS
    12 7900 JAMES
    13 7902 FORD
    14 7934 MILLER
    15 6666 XYZ (new row)

    15 rows selected.


    scott@oracle> select rn, empno, ename from
    2 ( select empno, ename, rownum rn from emp )
    3 where mod(rn,2)=1;

    RN EMPNO ENAME
    ---------- ---------- ----------
    1 7369 SMITH
    3 7521 WARD
    5 7654 MARTIN
    7 7782 CLARK
    9 7839 KING
    11 7876 ADAMS
    13 7902 FORD
    15 6666 XYZ (new row)
    8 rows selected.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width