Re: Rownum (was something else)

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 04 Oct 2005 11:32:05 -0700
Message-ID: <1128450719.315521@yasure>

mikharakiri_nospaum_at_yahoo.com wrote:
> DA Morgan wrote:
>

>>>ROWNUM is a hack that predated analytical SQL extensions, and some of
>>>these extensions do make sence for an end user. Otherwise, how do you
>>>express the query
>>
>>Without using ROWNUM please provide the equivalent to the following:
>>
>>SELECT *
>>FROM t
>>WHERE rownum < 37;

>
>
> select * from (
> select row_number() over (order by 0) rn
> from t
> ) where rn < 37
>
> As far as performance concerned, there is no reason why it can't be the
> same.
>
> Note, that unlike rownum hack, it would also provide an answer with
> negated predicate
>
> where rn >= 37
>
> and people would stop asking why rownum doesn't return any rows.
>
> BTW, I'm not fan of analytics, but having two constructs in the
> language doing the same thing is just messy.

Cute but I will cry foul (or perhaps fowl).

Source:
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions137.htm#i86310 "It assigns a unique number to each row to which it is applied"

Source:
"ROWNUM pseudocolum returns a number indicating the order in which Oracle selects the row from a table"

And precisely how are these substantively different? ;-)

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Tue Oct 04 2005 - 13:32:05 CDT