Re: Selecting every other code.... why wont THIS work?

"elziko" wrote...
> If I do the following:
>
> SELECT testtime, MOD(rownum,2) FROM testdata
>
> I get the following output:
>
> 0.0 1
> 0.1 0
> 0.2 1
> 0.3 0
> 0.4 1
>
> and so on. So by my reckoning, in order to select only every OTHER row in
> this table I should be able to do:
>
> SELECT testtime FROM testdata WHERE MOD(rownum,2) = 1
>
> But it returns no records! I've obviously missed something here?!?
>
> Cheers,
>
> elziko
>
>

Hi,

I'm not quite sure, but I think it's because Oracle assigns the rownum when the results are already fetched. Here's a little example:
-- Select everything from the table:
12:50:50 to85e>select wert, mod(rownum,2) from kons;

     WERT MOD(ROWNUM,2)
--------- -------------

        1             1
        2             0
        3             1
        4             0
        5             1

 real: 50

• Your second SELECT statement: 12:51:24 to85e>select wert from kons 12:51:36 2 where mod(rownum,2)=1;

     WERT

        1

 real: 50
-- The solution:
12:52:04 to85e>select wt, rn from (select wert wt, rownum rn from kons) 12:52:23 2 where mod(rn,2)=1;

       WT RN
--------- ---------

        1         1
        3         3
        5         5

 real: 50
12:52:26 to85e>

Hope it helps,
Guido

PS: corrections are welcome. Received on Mon Feb 03 2003 - 06:04:27 CST