Rownum (was something else)

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. Received on Tue Oct 04 2005 - 13:15:49 CDT