Re: How to implement oracle like rownum(function or seudocolumn)

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
> My humble guess is that c) is also the reason why the ANSI didn't find a
> ROWNUM desirable.

I've never understood what the conceptual model is for Oracle's rownum.
Where along the SQL operational pipeline (FROM / WHERE / GROUP BY /
aggregate / compute output columns / ORDER BY) is it supposed to be
computed? To be useful for the often-requested purpose of nicely
labeling output with line numbers, it'd have to be assigned
post-ORDER-BY, but then it doesn't make any sense at all to use it in
WHERE, nor in sub-selects.

A function implemented as per Michael's example would not give the
results that I think people would expect for

SELECT rownum(), * FROM foo ORDER BY whatever;

unless the planner chances to do the ordering with an indexscan.
If it does it with a sort step then the rownums will be computed before
sorting :-(

regards, tom lane