From: smac@svbank.com (Stewart)
Newsgroups: comp.databases.oracle
Subject: Presort Oracle table before the Select Query
Date: 5 Jun 2002 18:24:33 -0700
Organization: http://groups.google.com/
Lines: 39
Message-ID: <c12c9cf6.0206051724.a1cbb96@posting.google.com>
NNTP-Posting-Host: 12.24.41.132
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1023326674 8692 127.0.0.1 (6 Jun 2002 01:24:34 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 6 Jun 2002 01:24:34 GMT


I am trying to select a group of records from an Oracle Database.  I
want to pre-sort it first before selecting it because the recordsets
in the database are not in order.  The only way I know how to issue a
First SELECT and then an INNER SELECT so that the recordsets return in
the INNER SELECT is inorder. An Example of it is below.

SELECT and INNER SELECT: to presort the result before select the final
recordset.

SELECT DISTINCT id, name, address, areacode, 
FROM member
WHERE id in (SELECT id from member where areacode >='408' AND areacode
<='415')
AND rownum <6
ORDER BY id ASC

When user clicks next on the page here is a new query:

SELECT DISTINCT id, name, address, areacode, 
FROM member
WHERE id in (SELECT id from member where areacode >='408' AND areacode
<='415')
AND id>'some number' AND rownum <6
ORDER BY id ASC

I need the INNER SELECT to pre-sort the table in order for the
"id>'some number' to work when the user clicks next.  If the table is
not pre-sorted, then the first 5 resultset returned is not accurate. 
When the user clicks next, then it just grab the last id of the 5
resultset and append it to the 'some number' for a new query.

I hope this is not too confusing.  Is there another way to pre-sort
Oracle table first without using the inner select because it's very
slow?

Please advise.

Thanks,
Stewart