Re: Need Help with PL/SQL
Date: 1996/10/25
Message-ID: <3270bb24.43191986_at_dcsun4>#1/1
On Fri, 25 Oct 1996 10:41:46 -0700, dan merryman <dan.l.merryman_at_bangate1.tek.com> wrote:
>Garry M. Filimonov wrote:
>>
>> Thomas J. Kyte <tkyte_at_us.oracle.com> wrote in article <326d9737.9779201_at_dcsun4>...
>> >
>> > On Wed, 23 Oct 1996 09:56:17 -0400, "Kevin S. DeWitt" <dewitt_at_interramp.com>
>> > wrote:
>> >
>> > >Thomas J. Kyte wrote:
>> > >> (... SELECT skipped ...)
>> > >
>> > >Will your solution work if there is an ORDER BY
>> > >involved? My understanding of rownum is that it is
>> > >derived prior to the sort.
>> >
>> > Correct, you can't use rownum and order by at the same time. You can use group
>> > by and rownum together in the above fashion tho. The rownum will be evaluated
>> > AFTER the group by has taken place.
>> >
>> >
>> > Thomas Kyte
>> > Oracle Government
>>
>> As I've thought ROWNUM is proccessed BEFORE "GROUP BY" too, because
>> "WHERE clause", where it use, evaluate first. Correct me if it isn't truth.
>>
>> --
>> G'luck !
>> -----------------------------------------
>> Garry M. Filimonov,
>> Project Manager & Oracle DBA,
>> LASU TRINITI, Troitsk, MR, 142092, Russia
>> garry_at_triniti.troitsk.ru
>> +7(095)334-0408Well, obviously the best way to be sure is try it with a few small
>examples (which I havent taken the time to do) but...
>
>The inner select is esentially a view (an in-line view). The rownum is
>acting on rows returned from that view. If the 'group by' or 'order by'
>clause is part of the view definition (which it is in this case) it
>should not affect the rownum of records coming from that view.
>
>Make sense? Comments?
thats right, if you
select a, b, count(*)
from T
where rownum < 100
group by a, b
Rownum (like any predicate) will be evaluated prior to the group by, on the other hand:
select *
from ( select a, b, count(*)
from T
group by a, b )
where rownum < 100
forces the evaluation of the inline view prior to the predicate where rownum < 100 being evaluated. It has to be this way, the first question is very very different from the second.
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Fri Oct 25 1996 - 00:00:00 CEST