Re: How to implement oracle like rownum(function or seudocolumn) - Mailing list pgsql-hackers

From Nicolas Barbier
Subject Re: How to implement oracle like rownum(function or seudocolumn)
Date
Msg-id b0f3f5a10604111651i2b3f3747oe348d22d2d54f925@mail.gmail.com
Whole thread Raw
In response to Re: How to implement oracle like rownum(function or seudocolumn)  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
2006/4/8, Tom Lane <tgl@sss.pgh.pa.us>:

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

I don't know about Oracle or ROW_NUM, but SQL apparently defines
ROW_NUMBER() OVER (..) (see
<url:http://en.wikipedia.org/wiki/Select_(SQL)#ROW_NUMBER.28.29_window_function>)

This gives a number for each output row, according to some ordering
(in SQL, one cannot do ORDER BY in a subquery AFAIK). If used in a
subquery, one can then of course use the resulting column in the WHERE
clause of the outer query:

SELECT * FROM ( SELECT   ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,   columns FROM tablename
) AS foo
WHERE rownumber <= 10

(example stolen from the Wikipedia article linked above).

--
Nicolas Barbier
http://www.gnu.org/philosophy/no-word-attachments.html


pgsql-hackers by date:

Previous
From: "Joshua D. Drake"
Date:
Subject: Re: plpgsql by default
Next
From: Josh Berkus
Date:
Subject: Re: plpgsql by default