2006/4/8, Tom Lane <[EMAIL PROTECTED]>: > 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
