That's not going to scale well to large tables/results. I'd go with caching the rowid's from the query somewhere (db or file).
Tim. On Fri, Nov 30, 2001 at 10:52:34AM -0800, Brent Cowgill wrote: > I solved this problem just the other day after searching google. > I needed to have an order by and where clause preserved. > Here's what I came up with. Note, I haven't done any stress > testing on this query yet. If anyone comes up with an improvement > I'd be interested in getting a CC. > > SELECT > fields > FROM > table > WHERE > rowid IN ( > SELECT > rowid > FROM > (SELECT rowid > FROM table > WHERE criteria... > ORDER BY order... > WHERE rownum <= last_row_num_wanted > MINUS > SELECT > rowid > FROM > (SELECT rowid > FROM table > WHERE criteria... > ORDER BY order...) > WHERE rownum < first_row_num_wanted > ) > ORDER BY order... > > Siiskonen, Pekka wrote: > > >What is Oracle's equivalent to mySQL's "limit"? > > > >Sorry if considered off-topic! (I use DBD::Oracle and DBD::mysql) > > > >Pekka Siiskonen > > > >>-----Original Message----- > >>From: Bart Lateur [mailto:[EMAIL PROTECTED]] > >>Sent: 30. marraskuuta 2001 12:50 > >>To: [EMAIL PROTECTED] > >>Subject: Re: 1-10, 11-20 > >> > >> > >>On Fri, 30 Nov 2001 10:34:11 +0000, Tim Bunce wrote: > >> > >>>On Thu, Nov 29, 2001 at 03:08:32PM -0500, Charles Day wrote: > >>> > >>>>Might be slightly OT, sorry. I've been researching this > >>>> > >>for some time now. > >> > >>>>Exactly like search engines. Past ideas were to create a > >>>> > >>temporary db or > >> > >>>>use cursors. Anybody have ideas? > >>>> > >>>http://search.cpan.org/doc/TIMB/DBI_Talk5_2001/sld059.htm > >>> > >>You've got some pretty good ideas up there... My favourite is "Select > >>and cache only the row keys, fetch full rows as needed". > >> > >>But you might not need the cache, then. When combined with the first > >>idea, "re-execute query and discard unneeded", this can become: > >> > >> Re-execute full query only fetching the row id's. > >> Count and discard (this can be done in a perl array), > >> and then fetch the full rows only for those rows you want. > >> > >>-- > >> Bart. > >> > > > > > > -- > Regards, > Brent > > interactivetools.com, inc. > Tel: (604)689-3347 - Fax: (604)689-3342 - Toll Free: 1(800)752-0455 > Software for your Website - http://www.interactivetools.com/ > > Attachments accepted: TXT, HTML, RTF, PDF > I do not accept attachments which are capable of harbouring viruses, > for example Word for Windows. Please use File/Save As RTF or HTML. > > > >
