Ixora Answers - Top-N query in 8.0.5

From:	Steve Adams
Date:	30-Mar-2001 06:31
Subject:	Top-N query in 8.0.5

There are several ways to do this, but I think that the following is the closest to what you are looking for ...

    SQL> select
    2    inverse * -1  empno,
    3    ename
    4  from
    5    ( select
    6        empno * -1  inverse,
    7        ename
    8      from
    9        emp
    10      group by
    11        empno * -1,
    12        ename
    13    )
    14  where
    15    rownum < 5
    16  /

    EMPNO ENAME
    ---------- ----------
    7934 MILLER
    7902 FORD
    7900 JAMES
    7876 ADAMS

    SQL>

I need a suggestion for the query below.

    select * from (select * from emp order by empno desc) where rownum < 5

This query works fine in Oracle 8.1.5. The order by clause in the subquery is not a problem. The purpose is to first order by and then get the first 4 rows. The same query in Oracle 8.0.5 conks because of the order by clause in the subquery. Can you please let me know how to achieve the same in 8.0.5.