depending on your application you could just write a query ordered by whatever your field is, then loop through the first 20. The problem I came across is assuming I wanted to display other rankings, say 21-40, or 500-1000, or some odd range not starting at the top.
Mysql (a smaller, open source database) has extended select statements with a limit clause which accomplishes this job neatly, eg
select * from emp order by whatever limit 20;
would return only the 20 records
select * from emp order by whatever limit 10, 50;
would return records 10-60. (start at 10, go for 50 records. at least that's the syntax off the top of my head).
It would be nice if this small feature was added to oracle so embedded queries with rownum's weren't required.
If u have oracle 8.1.6 u can use the rank function.
to select top(n)
select emp, salary
from ( select emp_name emp, emp_salary salary,
rank() over (order by emp_salary desc) as rank1
where rank1 < 10
replacing rank1 by
rank1 < 20 and rank1 > 10 retrieves the between values.
I have tested it.
I haven't used this is production yet so cannot comment on timing. I use the conventional rownum way as suggested above.
in case there is a a tie, rank will eliminate one so use dense_rank in such cases. I am not sure whether the tie is defined as duplicate in select(same employee same salary) or duplicate in order by ( same salary diff employees).
There are some more cool statistical stuff in 8.1.6 that u might like to check out.