Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Sorting Large table, small select set

Re: Sorting Large table, small select set

From: Philippe <parnaud_at_yahoo.com>
Date: Fri, 12 Mar 1999 10:37:28 +0100
Message-ID: <7can99$7vf$1@concorde.ctp.com>


Try this,

SELECT * FROM
(SELECT /*+ RULE */ Y.* , ROWNUM ROWCOUNT FROM Y , DUAL D WHERE TO_CHAR(DATE, 'YYYYMMDD') ¦¦ ACCTNUM = D.DUMMY (+)) X WHERE X.ROWCOUNT BETWEEN 1 AND 20 for the next 20 records :
SELECT * FROM
(SELECT /*+ RULE */ Y.* , ROWNUM ROWCOUNT FROM Y , DUAL D WHERE TO_CHAR(DATE, 'YYYYMMDD') ¦¦ ACCTNUM = D.DUMMY (+)) X WHERE X.ROWCOUNT BETWEEN 1 AND 20
SELECT * FROM
(SELECT /*+ RULE */ Y.* , ROWNUM ROWCOUNT FROM Y , DUAL D WHERE TO_CHAR(DATE, 'YYYYMMDD') ¦¦ ACCTNUM = D.DUMMY (+)) X WHERE X.ROWCOUNT BETWEEN 21 AND 40 HTH, Philippe

Mark Foley wrote in message <7ca6gk$bpt_at_chronicle.concentric.net>...
>I have a database for which a certain recordset contains 20,000 records. I
>am
>displaying 20 or so records at a time to the user. These records need to be
>displayed
>in a sorted manner (ie ORDER by DATE,ACCTNUM).
>
>I had been using something like SELECT X From Y Where Rownum < 20 ORDER by
>DATE,ACCTNUM;
>
>only to realize that the ORDER by only works on the 20 rows.
>
>What I really want is to be able to return the 1st 20 records, then the
next
>20 records and so on.
>
>The only way I can come up with to do this is to read the entire recordset
>of 20,000 records and
>scroll through and pick up the ones I want.
>
>This can't be the optimum way to do this. Isn't there some way to enforce a
>sorting or order on the
>table either when selecting records, or perhaps even when initially
>inserting the records? This seems
>like something that would be required in many applications.
>
>I am reading records through ADO/Active Server pages and displaying them to
>the user through
>the browser interface.
>
>Any comments appreciated.
>
>
Received on Fri Mar 12 1999 - 03:37:28 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US