Re: insert statement using order by

A copy of this was sent to "Stephen Pillow" <stephenpillow_at_yahoo.com> (if that email address didn't require changing) On Wed, 13 Oct 1999 09:49:26 -0500, you wrote:

>This explains why I was trying to insert using order by
>
>I am trying to accomplish the following:
>
>1. Sort data in a table
>2. Select rows 50 - 100 of the sorted data
>3. Return only rows 51-100 to the app server
>
>I want the sorting to take place on the database and I do not want to return
>all rows to the app server.
>
>We are using JDBC and oracle 8i.
>
>Here is the path I was heading down when I sent the previous question.
>
>Oracle 8i has temporary tables. The temporary tables can work so that each
>process inserting(for this example) data into the temporary table does so in
>its own memory space. Once commit() is called, the data that was inserted
>is removed from the temporary table.
>
>Example,
>
>User A requests to see rows 51-100 of the data sorted by name.
>User B requests to see rows 201-250 of the data sorted by description.
>
>They perform these requests at exactly the same time.
>
>This is the order of events:
>1. The data from the physical table is inserted into the temporary table
>sorted and a field containing the row number is added.
>2. The appropriate range of rows is selected (i.e. 51-100) and returned
>to the app server
>3. Commit is called and the sorted data is removed.
>

consider this:

scott_at_8i> select ename, job, sal from emp order by 1, 2, 3   2 /

ENAME JOB SAL
---------- --------- ----------

ADAMS      CLERK           1100
ALLEN      SALESMAN        1600
BLAKE      MANAGER         2850
CLARK      MANAGER         2450
FORD       ANALYST         3000
JAMES      CLERK            950
JONES      MANAGER         2975
KING       PRESIDENT       5000
MARTIN     SALESMAN        1250
MILLER     CLERK           1300
SCOTT      ANALYST         3000
SMITH      CLERK            800
TURNER     SALESMAN        1500
WARD       SALESMAN        1250

14 rows selected.

scott_at_8i> select * from
  2 ( select rownum r, a.* from ( select ename, job, sal

  3                                    from emp
  4                                   order by ename, job, sal ) a
  5       where rownum <= 10 )

         R ENAME JOB SAL ---------- ---------- --------- ----------

         5 FORD       ANALYST         3000
         6 JAMES      CLERK            950
         7 JONES      MANAGER         2975
         8 KING       PRESIDENT       5000
         9 MARTIN     SALESMAN        1250
        10 MILLER     CLERK           1300

6 rows selected.

That shows how to get rows 5-10 of a sorted result set IN ORACLE8i release 8.1 (this will *not* work in prior releases).

>There are no concurrency issues because each process that performs the sort
>and select operates in its own space. This is how User A and User B can
>request different sort criteria and range of rows and get the appropriate
>response.
>
>However, as you guys have informed me, even if I could insert data into the
>temporary table sorted, Oracle does not guarantee to store it that way.
>
>So, any suggestions on how to accomplish this would help. I believe the
>answer probably deals with using cursors.
>
>Thanks,
>Stephen
>
>
>
>> Hi all,
>>
>> I would like to be able to take data from one table and put the data in
>> another table sorted. I have attempted to use the "insert into tablename
>> (select * from othertable order by desc)". However, Oracle 8 and 8i do
 not
>> support this(it is a bug). Oracle did support the use of order by in a
 sub
>> query in release 7.3.4.
>>
>> I am looking for a solution that does not involve exporting the table data
>> to file sorted and then loading it using sql loader.
>>
>> Thanks for your help,
>> Stephen
>>
>>
>>
>

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