Continuing the ongoing saga... I wrote that the "top-n" queries in
Oracle can be handled by using a nested SELECT with an ORDER BY, then
using ROWNUM in the external WHERE clause. But...
> I just tried it on our instance and it doesn't work. We are on
> Oracle7 Server Release 7.3.4.5.0. Is this something that only works
> in Oracle 8+ ? It doesn't seem to like the ORDER BY clause in the
> FROM clause query. If I take ORDER BY dsc out the query runs but
> not with the desired results. What gives?
Well, I saw this feature (for doing "top-n" queries) listed as one of
the reasons to migrate to Oracle8i:
http://www.elementkjournals.com/dbm/0003/dbm0031.htm
So I wouldn't be surprised if this were indeed a version 8 feature.
Oh, and here's the 8.1.5 documentation on this feature:
http://oradoc.photo.net/ora81/DOC/server.815/a68003/rollup_c.htm#33316
(This is obviously where the docs I referenced earlier are pointing,
but it seems to be removed from the 8.1.6 documentation set. Uhg.)
> | SQL>r
> | 1 SELECT *
> | 2 FROM ( SELECT * FROM codes ORDER BY dsc )
> | 3* WHERE ROWNUM <= 10
> | FROM ( SELECT * FROM codes ORDER BY dsc )
> | *
> | ERROR at line 2:
> | ORA-00907: missing right parenthesis
>
> If I take ORDER BY dsc out the query runs but not with the desired
> results:
>
> | SQL>r
> | 1 SELECT *
> | 2 FROM ( SELECT * FROM codes )
> | 3* WHERE ROWNUM <= 10
>
> What gives?
As I said above, it probably is an Oracle 8 feature.
You can always "brute force" this programmatically; just grab them
all, in order, then display only the ones you're interested in. You
can always finish the handle after you've found those.
If the records are very wide, you might save some time by returning
only an identifying field in the desired sort order; after you grab
the id fields for the relevant range of records, you can then fetch
exactly those using an IN clause or similar. (But I'd guess that the
extra execute would be more costly, unless you have lots of very wide
fields in the SELECTed columns!)
Hm. Here's an arcane way of doing it:
http://www.caribdata.co.uk/additional/new_user.html#top_n
And a variant on the same:
http://www.cstone.net/~phh5j/sqlfaq.html#TOP
(Both were the result for a google.com search for "oracle top-n
query".)
If you have a lot of records, however, I'd expect that asking Oracle
to just give you all of them, in sequence, would be faster. Both of
the above methods appear to be quadratic algorithms, while a simple
ordered query should run in only n-log-n time.
The XSQL servlet has "max-rows" and "skip-rows" values for this
reason:
http://otn.oracle.com/tech/xml/xsql_servlet/htdocs/relnotes.htm#ID2827
But I would guess it's doing exactly as I suggest above -- get the
whole thing, and only show the ones you care about (and quit early, if
possible).
In your case, I'd just do:
| my $sql = "SELECT * FROM codes ORDER BY dsc";
| my $sth = $dbh->prepare($sql);
| $sth->execute();
| my $n = 0;
| while (my $cur = $sth->fetch())
| {
| $n++;
| next if $n < $start_row_num;
| last if $n > $last_row_num;
| # do stuff with $cur here
| }
| $sth->finish();
Hope this helps,
t.
