Re: SELECT paging output

Hi,

You omitted --+ RULE hint from my inner select. It is necessary for merge join.

Andrew Protasov

> 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