This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
To: Tony Foiani <anthony_foiani@non.hp.com>
From: Tony Foiani <anthony_foiani@non.hp.com>
Subject: Re: Limiting the number of records selected.
Date: 13 Mar 2001 12:41:06 -0700
>>>>> "Bill" == Bill OConnor <bill_oconnor@yahoo.com> writes:
Bill> I want to use a subset of the selected rows on a webpage. For
Bill> instance if the query returns 100 rows I want show just 10 of
Bill> them on the page, 11-20 on the next etc.
>>>>> "Tony" == Tony Foiani <anthony_foiani@non.hp.com> writes:
Tony> Look at the discussion of the ROWNUM pseudo-column, in the
Tony> Oracle SQL reference.
Someone sent me some private e-mail on this topic, and I thought the
list would be interested in seeing my response.
> ROWNUM is useless if you have an ORDER BY clause.
Incorrect. Just use the "ORDER BY" on a sub-SELECT, then grab the
range of rows you want in the outer SELECTs. This is exactly what the
documentation [1] tells you to do. So, a top-10 query would be:
| SELECT *
| FROM ( SELECT * FROM my_table ORDER BY my_col )
| WHERE ROWNUM <= 10;
While grabbing the next 10 is a bit harder; I would probably just grab
all of them out to the end, and only show the last 10 (possibly adding
ROWNUM as a SELECTed column, so I could trivially match on it).
If you know all the columns, you could do it all in SQL again:
| SELECT col1, col2, col3
| FROM ( SELECT ROWNUM AS row_num, col1, col2, col3
| FROM ( SELECT col1, col2, col3 FROM my_table ORDER BY col1 )
| WHERE ROWNUM <= 20 )
| WHERE row_num >= 11;
> I think Mr. Howard's sollution [SELECT TOP n] will work.
In Oracle? I doubt it. TOP isn't even a reserved word!
t.
[1] Oracle 8i SQL Reference, Release 2 (8.1.6), A76989-01; Chapter 2
Basic Elements of Oracle SQL, Section Pseudocolumns
===