how to display only 20 items of the result in oracle sql?

Question

Is it possible to display only 20 items of the whole result? I know of a query "rownum>=20" but that only makes the sql check only the first 20 rows in the table. But i want the sql to check my whole table and display only the highest 20 entries.

To be clear, I have a table with job description like Title, description, agency, salary_min, salary max. I want to display the 20 highest paying jobs.

Answer 1

The "highest" 20 entries suggests a sort. You would do something like this:

select t.*
from (select t.*
      from table t
      order by highestcol desc
     ) t
where rownum <= 20;

If you are using Oracle 12g or more recent, you can use the fetch first clause instead:

select t.*
from table t
order by highestcol desc
fetch first 20 rows only;

Answer 2

First sort(order by) and then use rownum function.

Answer 3

select * from  
( select * 
  from emp 
  order by field ) 
where ROWNUM <= 20;

Answer 4

select a.fld1, a.fld2 
from 
  ( select fld1, fld2
    from mytable 
    order by 1 desc) a
where rownum <21;

This is one way to do what I think you asked for. There are others ways some other posters can provide.