Re: Nested queries with an order by clause

A copy of this was sent to Marvin <marteaga76_at_my-deja.com> (if that email address didn't require changing) On Fri, 05 Nov 1999 19:37:58 GMT, you wrote:

>I need to select everything from a huge table ( 1.5 million records )
>( its a book database, shhhh, don't tell anybody )
>Since I'll be displaying all of these a few pages at a time. I want
>to be able to get just the page I want out of the whole table. The
>problem is that the whole list has to be ordered by title.
>This is what I tried to do:
>
>select title,isbn,listprice
>from
>(
> select title,isbn,listprice,inner_rownum
> from
> (
> select title,isbn,listprice,rownum as inner_rownum
> from book
> order by title /*hint: this ones the stinker*/
> )
> where inner_rownum > BEGING_PAGE
>)
>where inner_rownum < END_PAGE
>
>The proble is that oracle ( or sql in genreal ? ) dosent like that
>"order by" in the inner query. Any suggestions?
>Remember the requirement is to get a sub range of the whole table
>ordered by title. Just moving the "order by" clause to the outside
>dosent work since each page will be ordered by title, but the list as a
>whole will not be ordered properly.

This is supported in Oracle8i, release 8.1. It is *not* available in 8.0 or before:

  1 select * from
  2 (
  3 select a.*, rownum r
  4 from ( select ename, empno, sal from emp order by sal ) a   5 where rownum <= 4
  6 )
  7* where r >= 2
scott_at_8i> /

ENAME EMPNO SAL R ---------- ---------- ---------- ----------

JAMES            7900        950          2
ADAMS            7876       1100          3
WARD             7521       1250          4

scott_at_8i> select ename, empno, sal from emp order by sal;

ENAME EMPNO SAL
---------- ---------- ----------

SMITH            7369        800
JAMES            7900        950
ADAMS            7876       1100
WARD             7521       1250
MARTIN           7654       1250
MILLER           7934       1300
TURNER           7844       1500
ALLEN            7499       1600
CLARK            7782       2450
BLAKE            7698       2850
JONES            7566       2975
SCOTT            7788       3000
FORD             7902       3000
KING             7839       5000

14 rows selected.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Nov 05 1999 - 14:21:23 CST