Re: Top N in Oracle 8.0.5

Try (from the FAQ at Jonathan's site.) I'd add one thing that has been pointed out to me since I wrote this. As well as the table being analyzed the column(s) on which you are ordering need to be not null.

The alternative solution for older versions of Oracle is to use hints to nudge Oracle into returning rows in the correct order before we filter them , the typical hint to use in this case is INDEX_DESC(TABLE_NAME INDEX). So in our example the statement would be

a..
a.. SELECT qty,title_id FROM
(SELECT /*+ INDEX_DESC (SALES SALES_QTY_IDX) */ qty,title_id FROM sales) WHERE rownum < 6;
Where SALES_QTY_IDX is an index on the quantity column of the sales table. For this hint to work the table has of course to be analysed.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************



"ivan vasquez" <ivan_at_itos.uga.edu> wrote in message
news:afq30j$f5r$1_at_cronkite.cc.uga.edu...


> Hi,

>

> How do I create a top-N query in Oracle 8.0.5?

>

> The top-N has to happen after sorting the input, but ordered subqueries

> weren't allowed prior to 8i. All the documentation I've found is related

to


> 8i, still no luck with this version!

>

> INSERT INTO PA_ASSIGN

> SELECT * FROM (

>     SELECT *

>     FROM PA_SELECT

>     WHERE code = xPlSqlVariable1

>     ORDER BY col1, col2, col3

> WHERE ROWNUM <= xPlSqlVariable2;

>

> Any hints are greatly appreciated.

>

> Thanks,

> Ivan.

>

>