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

On Sat, Apr 08, 2006 at 03:04:40PM -0400, Tom Lane wrote:
> 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.

AFAIK rownum() is Oracle's solution to doing LIMIT ... OFFSET from
before those were ANSI. rownum() is applied as rows are leaving the
relevant node, which means you can't use rownum() in any part of a
SELECT statement other than the SELECT clause (you can't even use it in
a HAVING clause afaik, though I would think you should be able to). So,
if you want to actually do anything useful with rownum(), you have to
use it in a subquery and then operate at a higher level:

SELECT * FROM (SELECT rownum() AS row_number, * FROM table) z ORDER BY
row_number;

> 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 :-(

I think you're right.

If people are that hot-to-trot about having Oracle compatable rownum()
in PostgreSQL, perhaps EnterpriseDB has some code they could share.
Though I think it'd be better to understand what people actually want
this info for. Personally I think having a rank function (or a complete
suite of analytic functions) would be far more useful.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461