Re: Top N in Oracle 8.0.5

I might be wrong ... and cannot test this .. but I remember using distinct clause to sort the subquery results.
Oracle used to allow distinct but not order by ... so this workaround. Since distinct effectively does an order by .. it used to work in a lot of cases.

Something like this:

Select col1, col2, col3, col4, col5
from (select distinct col1, col2, col3, col4, col5, rownum dummy from mytable)
where rownum < 6
/

The addition of rownum column in the subquery effectively makes sure that an order by is done without it eliminating any distinct rows (rownum ... so all rows will be distinct) :)

Anurag

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3d217cfe$0$8512$ed9e5944_at_reading.news.pipex.net...
> 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.
> >
> >
>
>
Received on Tue Jul 02 2002 - 10:33:38 CDT