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.