CODEWITHSUNDEEP
sqldatabaseoracle-databasetop-n
SuperCoder
SuperCoder

Reputation: 262

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

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.

Upvotes: 0

Views: 3670

Answers (4)

Dany Aguilar
Dany Aguilar

Reputation: 284

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

Upvotes: 1

jim mcnamara
jim mcnamara

Reputation: 16389

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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;

Upvotes: 4

Deva
Deva

Reputation: 11

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

Upvotes: 1

Related Questions