Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How can I select several result

Re: How can I select several result

From: Alexander Penev <alex_at_cska.net>
Date: Tue, 29 Aug 2000 22:40:43 GMT
Message-ID: <39AC3BDE.A6FB457F@cska.net>

Try the following:

select * from (select a.*, rownum rid from (select * from emp order by ename) a) where rid between 5 and 10

I don't know if the performance is ok , but it works in the way you want (I hope ;-) )
Version must be >= 8.1.5
HTH r1sc_at_my-deja.com wrote:

> '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 - 17:40:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US