Re: advanced SQL

From: Wanderley <wces123_at_yahoo.com>
Date: Wed, 15 Jan 2003 01:32:35 GMT
Message-ID: <TS2V9.35936$aG4.1930319@twister.austin.rr.com>

Alberto wrote:

>>You must mean the analytical functions (8i and up):
>>
>> select
>>    row_number() over (partition by charfield order by charfield),
>>    charfield,
>>    numfield,
>> from bc4j.test

>
>
> That's it! Thanks.
>
> In the mean time, I solved the problem with a nice trick.
>
> Something like this:
>
> select counter.c,Tt.*
> from bc4j.test tt,
> (select rownum c from bc4j.test) counter
> where counter=(select count(*)+1
> from bc4j.test
> where tt.charfield=charfield
> and rowid<tt.rowid)
>
> It works! Pheraps it's slower that with partition, but it works! Nice.
>
> Now I try with over.
>
> thank you very much.

Or:

SELECT MOD(ROWNUM+2,3) + 1, *
FROM TEST; Received on Tue Jan 14 2003 - 19:32:35 CST