Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: LIMITing output
Hello,
I have already had this problem. Here is what Oracle says : (From online generic documentation 7.3.4)
<cut>
DECLARE
CURSOR c1 IS SELECT empno, sal FROM emp
WHERE sal > 2000 AND ROWNUM < 10; -- returns 10 rows
The value of ROWNUM increases only when a row is retrieved, so the only meaningful use of ROWNUM in a WHERE clause is
... WHERE ROWNUM < constant;
For example, the following condition cannot be met because the first nine rows are never retrieved:
... WHERE ROWNUM = 10;
</cut>
Effectively if I try :
SQL> select rownum,poll, GROUPNAME from poll;
ROWNUM POLL GROUPNAME
---------- ---------- ---------------
1 3 A 2 4 B 3 5 C 4 6 5 7 D 6 8 E 7 10 8 11 F 9 12 10 13 11 14 G 12 15 13 16 14 17 15 18
Then :
SQL> select rownum,poll, GROUPNAME from poll where rownum > 5 ;
no rows selected
But we can use a trick :
1 select rownum,poll, GROUPNAME from poll
group by rownum, poll,groupname having rownum >5 and rownum < 10
6 8 7 10 8 11 9 12
In Mysql there is the extremely usefull 'limit 10,20' clauses. I never found the equivalent in Oracle.
If somebody knows a better solution, do not hesitate.
B. Polarski
In article <38B16EEF.A26CB1C4_at_fast.no>,
kev <kevin.porter_at_fast.no> wrote:
> Hi,
>
> I've been trying to find out for a while how to select the Nth M rows
> from a resultset, for the purpose of paginating results on a web page,
> eg show news stories 1-10 on the first page, user clicks 'More', gets
> news stories 11-20, etc.
>
> I never found a way to do it in Oracle 8.0.5 (mainly because of
> ORDER BY clauses not being allowed in subqueries) but I was told that
8i
> would be better for this because it does allow ORDER BY clauses in
> subqueries. I've upgraded to 8i now (on Red Hat 6.1) and a quick test
> failed to show that ORDER BY clauses in subqueries are indeed
supported.
>
> How do you people usually tackle the problem of paginating results on
> web pages? Does 8i indeed support ORDER BY clauses in subqueries?
>
> TIA,
>
> - Kev
>
>
--
B.Polarski
http://www.geocities.com/bpolarsk
Email : bpolarsk_at_yahoo.com
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Mon Feb 21 2000 - 14:06:25 CST