SQL Query

[aph]

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

[Dan1]

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

[raghud]

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

[aph]

I have to use only SQL not Pl/sql.

[raghud]

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

[aph]

Thanks for your solution. I will test it and let you know.

Thanks.

[ales]

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

[aph]

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.

[tamilselvan]

You will get a different result set at different time when you use rownum in a SQL statement.

[aph]

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.