Closed dhobbs closed 12 years ago
I'd just like to add that your query would only work if you're looking for the first x
rows from ROWNUM
. If you want to do something like LIMIT m,n
where you're looking for an offset and a number of rows (Oracle calls this Pagination), you'll have to do something entirely trickier:
select *
from ( select /*+ FIRST_ROWS(n) */
a.*, ROWNUM rnum
from ( your_query_goes_here,
with order by ) a
where ROWNUM <=
:MAX_ROW_TO_FETCH )
where rnum >= :MIN_ROW_TO_FETCH;
As per the Oracle AskTom article (see Pagination with ROWNUM subsection)
Your assesment is correct, but this is because there is no oracle specific compilation whatsoever. If you wish to implement one, follow my lead here: http://offtopic.bestinclass.dk/post/2073377383/first-cut-at-an-extensible-compiler
This can be demonstrated by trying to run the following against an oracle db:
(-> (table db "foobar")(take 5)))
The code above generates 'SELECT foobar.* from foobar LIMIT 5'
The oracle syntax for the same result is:
SELECT foobar.* from foobar where rownum <= 5