Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Sorting Large table, small select set
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