Oracle 'ROWNUM' vs MySQL 'LIMIT'

[jdmartin74]

I am writing a web page that returns back a long list of results based on an Oracle DB.

Because I am getting many hundred results back, I would like to limit these on a per page basis and only show 100 records per page and have a 'Next Page' and 'Previous Page' option at the bottom as is very common.

In MySQL, I would issue the following SQL commands:

select * from my_table limit 0, 100

and then for the next page:

select * from my_table limit 100, 100

and the next page:

select * from my_table limit 200, 100

...and so on and so on...

However, I am unable to find an equivilent on Oracle. I have found the ROWNUM field, but I can only do the first page with this:

select * from my_table where rownum <= 100

I can't find a way of doing subsequent pages. i.e. getting results from the middle of the dataset. A SQL command of:

select * from my_table where rownum > 100 rownum <= 200

...returns no records.

Any help would be appreciated.

 

[HimanB]

Hi,
Trey following:

Select rownum,empno,ename from
(select rownum,empno,ename from emp
 order by 1)
where rownum between 0 and 100;

HTH
Regards
Himanshu

 

[sem]

I agree with Himanshu in general, but you may successfully omit order by clause, because ROWNUM is calculated during fetching thus in any case the result set is sorted by ROWNUM. Though you should be informed that without explicit ORDER BY clause based on real, not pseudo, columns the sort order is neither stable nor even defined. So it's almost improbable but still possible that both queries return the same set


Regards, Dima

http://seminihin.narod.ru

 

[jdmartin74]

between 0 and 100 does work...however, to then do subsequent pages, such as between 100 and 200 does not.

 

[sem]

Follow the advice of Himanshu:

Select rownum,empno,ename from
(select rownum,empno,ename from emp)
where rownum between 100 and 200;

Regards, Dima

http://seminihin.narod.ru

 

[HimanB]

Hi,
I do not understand why it does not work.
Can you please explain what is the command you have used.

Regards
Himanshu

 

[sem]

Oops, I wasn't quite careful, don't follow my previous posts/ The code should be

Select empno,ename from
(select rownum rn,empno,ename from emp)
where rn between 100 and 200;

Regards, Dima

http://seminihin.narod.ru

 

[jdmartin74]

I'm getting closer I think, but this still doesn't work properly.

With the code suggested above, the 'between 100 and 200' does indeed now return some results. However, to confuse the issue, I have an ORDER BY clause. Thus, the rownum gets evaluated prior to the ORDER BY, so selecting rows 100 to 200 gives me rows 100 to 200 before the sort.

Any ideas?

 

[jaggiebunnet]

try

select * 
  from (select e.*, rownum r 
           from (select empno,ename from emp
                  order by empno) e
          where rownum < 101 )  -- increment this to 201 next
 where r > 0; -- increment this to 100 next

 

[jdmartin74]

Thanks very much. This has worked fine. Any idea if LIMIT is supported in Oracle 10g? Seems a long way round what must be a common problem.

Once again, help very much appreciated.

 

[jaggiebunnet]

I don't think it is. But if you have a look at the new features:

http://otn.oracle.com/pls/db10g/portal.portal_demo3?selected=1

You might see something there.