Re: SELECT paging output

From: Andrew Protasov <oracle_at_protasov.kiev.ua>
Date: 1998/11/30
Message-ID: <AEp3dOsK42@protasov.kiev.ua>#1/1

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> );

Statement processed.
SVRMGR>
SVRMGR> insert into sort_test(a) values(34); 1 row processed.
SVRMGR> insert into sort_test(a) values(12); 1 row processed.
SVRMGR> insert into sort_test(a) values(67); 1 row processed.
SVRMGR> insert into sort_test(a) values(37); 1 row processed.
SVRMGR> insert into sort_test(a) values(54); 1 row processed.
SVRMGR> insert into sort_test(a) values(78); 1 row processed.
SVRMGR> insert into sort_test(a) values(33); 1 row processed.
SVRMGR> insert into sort_test(a) values(97); 1 row processed.
SVRMGR> insert into sort_test(a) values(62); 1 row processed.
SVRMGR> insert into sort_test(a) values(41); 1 row processed.
SVRMGR> insert into sort_test(a) values(32); 1 row processed.
SVRMGR> insert into sort_test(a) values(99); 1 row processed.
SVRMGR> insert into sort_test(a) values(69); 1 row processed.
SVRMGR> insert into sort_test(a) values(23); 1 row processed.
SVRMGR> commit;
Statement processed.
SVRMGR>
SVRMGR> select a from sort_test order by a; A

---

        12
        23
        32
        33
        34
        37
        41
        54
        62
        67
        69
        78
        97
        99

14 rows selected.

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

4 rows selected.

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