Re: SELECT paging output

Hi,

It looks very interesting... but when I tried to insert for example : SVRMGR> insert into sort_test(a) values(9); 1 row processed.

and I run the script (with the few values) and I've not received the right order.

For example the result was :

SQL> select * from (
  2 select a, rownum rn
  3 from sort_test,dual
  4 where to_char(a) = dummy(+))
  5 where 4<=rn and rn<=6;

        A RN
--------- ---------

       54         4
       67         5
        9         6 

Best regards,

Erika Grondzakova

Andrew Protasov wrote:
>
> 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 Tue Dec 01 1998 - 00:00:00 CST