ROWNUM and ORDER BY

SQL> create table t ( x int );

Table created.

SQL>
SQL> insert into t
  2  select dbms_random.value(1,1000)
  3  from dual
  4  connect by level <= 100;

100 rows created.

SQL>
SQL> select count(*) from t;

  COUNT(*)
----------
       100

1 row selected.

--
-- DOES NOT WORK
--
SQL>
SQL> select * from t
  2  where rownum <= 10
  3  order by x desc;

         X
----------
       995
       909
       873
       871
       640
       555
       318
       197
       134
        70

10 rows selected.

--
-- CORRECT ALTERNATIVES
--

SQL>
SQL> select *
  2  from
  3    ( select * from t order by x desc )
  4  where rownum <= 10;

         X
----------
      1000
       995
       991
       977
       977
       971
       955
       909
       909
       900

10 rows selected.

SQL> select * from t
  2  order by x desc
  3  fetch first 10 rows only;

         X
----------
      1000
       995
       991
       977
       977
       971
       955
       909
       909
       900

10 rows selected.

SQL>
SQL> select x
  2  from
  3    ( select t.*,
  4         row_number() over ( order by x desc ) r
  5      from t  )
  6  where r <= 10;

         X
----------
      1000
       995
       991
       977
       977
       971
       955
       909
       909
       900

10 rows selected.