Home -> Usenet -> comp.databases.oracle -> Re: insert statement using order by

Re: insert statement using order by

From: Stephen Pillow <stephenpillow_at_yahoo.com>
Date: 1999/10/13
Message-ID: <%Z0N3.4584$7K4.36856_at_typ11a.deja.bcandid.com>#1/1 







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.



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

>

>

>

Received on Wed Oct 13 1999 - 00:00:00 CEST












Original text of this message