Re: How can I select several result

'm having the same problem with an application that I'm developing. I would like to be able to receive only a certain number of results at a time and still be able to request the remaining results at a later date using an offset. In mySQL this is very simple. You use the limit command at the end of any select statement and it will only pass back the
specified amount. Another use of this same command will allow you to specify the offset and the amount to be returned. I am aware of the rownum feature of oracle but it contains a major short coming that makes it useless in my scenario. "where rownum<#" will execute _before_ "order by" statements. This means that if you only want the largest 5 entries in some field and you execute "select some_field from some_table where rownum<6 order by some_field desc" then you will in fact get some random result because the order by is only executed after the rownum<6 statement has reduced the result set(which may or may not be the largest set in the table). Plus, since it is impossible to do "rownum>#" (I assume this is a result of some recursive madness) this technique is useless for specifying an offset. Needless to say, this is all very disappointing. I find it hard to believe that such a successful (and expensive!) db distro would lack this very basic functionality and I assume that I am merely ignorant of the proper solution. Any one willing to enlighten me and the rest of the poor basterds running into this problem will undoubtedly receive huge karma points.

r1sc

In article <8o5uu5$8ko$1_at_news.sinet.slb.com>,   "Yong" <yhuang_at_indigopool.com> wrote:
> Are you talking about something like this:
>
> select * from mytable where rownum < 100;
>
> Yong Huang
> yhuang_at_indigopool.com
>
> <dewbow_at_my-deja.com> wrote in message news:8nvcg4
 $30b$1_at_nnrp1.deja.com...
> > It's kown that we can use limit in MySQL so it's easy for us to show
> > several information from select result. We can use this: select
 items
> > from table where condition limit <start>,<records> in MySQL. How to
> > realize this function in ORACLE.
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Aug 29 2000 - 16:08:10 CDT