Re: [ORACLE v8.0] SELECT foo FROM foo LIMIT (10,20) ORDER BY foo;

not sure what 0 to 100 naturally sorted means. Did you try:
 1)select * from ( select my_table.*, rownum rnum from my_table order by my_column desc )

    where rnum >10 and rnum < 20 order by my_column desc;

because in case 1 you can't rely on what order it will come back in unless you specify it. (Okay the exception might be an IOT.) Jim
"Jean Bourtin" <Jean.Bourtin_at_nospam.org> wrote in message news:3c722267$0$16583$626a54ce_at_news.free.fr...
> Hi,
> as shown in the title, I am unable to make a function setting two limits
in
> a result set.
> Imagine a table that contains only numbers from 0 to 100 (naturally
sorted).
>
> If I set any these statements :
> --- --- --- ---
> 1)select * from ( select my_table.*, rownum rnum from my_table )
> where rnum >10 and rnum < 20 order by my_column desc;
>
>
> 2)select rownum, my_column
> from my_table
> where rownum < 20
> group by rownum,my_column
> having rownum > 10
> order by my_column desc;
>
> 3)select rownum,my_column from my_table
> where rownum < 20
> MINUS
> select rownum,my_column from my_table
> where rownum < 10 order by my_column desc;
>
> 4)select * from ( select my_table.*, rownum rnum from my_table order by
> my_column)
> where rnum between 10 and 20 desc;
> --- --- --- ---
> the result is always the same :
> result = [19,18,17,16,15,14,13,12,11]
> instead of this expected result:
> result = [89,88,87,86,85,84,83,82,81]
>
> IS THERE A PL/SQL FUNCTION ABLE TO HELP ME ?
>
> regards,
> JB.
>
>
>
>
>
>
>
>
Received on Tue Feb 19 2002 - 07:30:20 CST