Re: how to retrieve records from mth to nth rows in order form?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 20 Dec 1999 07:34:39 -0500
Message-ID: <qb8s5s834r4m3h8kint7rvrgnk7la74jg9@4ax.com>

A copy of this was sent to "lkw" <lkw_at_axiomatique.com> (if that email address didn't require changing) On Mon, 20 Dec 1999 11:23:17 +0800, you wrote:

>hi everybody,
>
>i hv some doubts here....hoping that ppl here can help me ....
>
>say i want to select records from row 1 to 5 of a table, so i wrote this sql
>statement:
>
>1. select * from tablename where rownum <=5
>minus select * from tablename where rownum <1;
>

that query is the same as simply:

select * from tablename where rownum <= 5;

the minus is not needed as where rownum < 1 is *never* true.

>if i want to sort it within the recordset (from row 1 to 5), i can simply
>issue another sql statement:
>
>2. select * from tablename where rownum <=5
>minus select * from tablename where rownum <1 order by something;
>

that will order the first 5 rows the select happens to pick off. rownum is assigned AFTER a where clause but BEFORE a sort/aggregate. That gets 5 rows and then sorts them. You want to sort the rows -- then get the first 5.

>but now my problem comes in....say the table has 100 records in total, so
>each time i just want to select 10 records out of the 100. At first i select
>from row 1 to 10, then 11-20,
>21-30, 31-40 ... and so on. The problems comes in bcoz of the sorting part.
>No.2 sql statement works fine but doen't meet my requirement, bcoz it only
>sorts within the recordset of row 1 to 10, but what i want is to get a
>record set from row 1 to 10 but the order should take all the other 90
>records into account as well. In short, what i want should be like this:
>
>i. select * from tablename order by something;
>ii. After obtaining this resultset, then when i issue the sql statement
>written above, it shall return me the recordset from row 1-20 in order.
>
>so, my question is: how to combine the above two steps into one single sql
>statement? izzit possible?
>

In Oracle8i, release 8.1 (and not before -- this query works in 8.1.5 and up) you can:

select *
from (select a.*, rownum R

        from ( select * 
                 from tablename 
                order by whatever ) a
       where rownum <= MAX_ROW_YOU_WANT
      )

where R >= MIN_ROW_YOU_WANT;

that

1. creates an ordered set
2. assigns rownum to each row after sorting
3. keeps rows that are less then the max row you want
4. throws out rows that are less then the min row you want.

>Any helps would be greatly appreaciated!!
>
>lkw.
>
>p/s: sorry so long winded....*grin*
>
>

--
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 Received on Mon Dec 20 1999 - 06:34:39 CST