Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SELECT paging output
Hi,
Try this approach from Jurij Modic. It uses implicit sort when merge join:
SVRMGR> drop table sort_test;
Statement processed.
SVRMGR> SVRMGR> create table sort_test 2> (a number 3> );
12 23 32 33 34 37 41 54 62 67 69 78 97 99
SVRMGR> SVRMGR> select * from ( 2> select --+ RULE 3> a 4> ,rownum rn 5> from sort_test,dual 6> where to_char(a)=dummy(+)) 7> where 4<=rn and rn<=7 8> ; A RN ---------- ---------- 33 4 34 5 37 6 41 7
Andrew Protasov
> Hello,
> I need to obtain records from an ordered (group by) select starting from
> a low bound row num and limited to a hupper bound number. I need to select
> es. records from my select ranging from the 40th to the 100th record into.
>
> I tried a query like
>
> SELECT fn, id
> FROM (
> SELECT employee_id id,
> name fn,
> rownum rn
> FROM employee_db
> WHERE age < 33
> )
> WHERE rn > 40 and rn < 100
>
> and it seems to works fine. But I can't do an ORDER BY on the nested
> SELECT, but I need an order.
>
> Probably will be useful to select into a temporary table, group by on it
> and then select rows also using rownum facility. But I am not sure this is
> the better answe to the problem.
>
> Any help?
>
> Thank you,
> Antonio Storino
>
>
Received on Mon Nov 30 1998 - 00:00:00 CST