iS THERE A top(X) FUNCTION IN oRACLE?

hI,

I want a query to return top 20 records, I know that there is no such fuinction in Oracle, but I read somewhere that there is in some new vesrion. What version it is?

I am using 8.1.5

Thanx!

Hi,
I dont think there is any function to give top records,basically you have to write a query.

From MetaLink Note:47915.1

With Oracle8i, the distinct clause is no longer necessary since it is now
possible to use an order by directly in an in-line view. Thus the above two
select statements become:

select ename, empno, deptno
from (select deptno, ename, empno from emp ORDER BY deptno, ename)
where rownum <= 5;

D.

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.

Here is the example out of the Oracle Dcoumentation for Top-N query

Select * from
(select class_id, crs_id, start_date
from classes
order by start_dat DESC)
where rownum < 10;


Eric Yen

Hi,

I tried the query and it works fine, but when I want to display a range of values say from 10 to 20 it doesn't work, and I don't understand why!

select rownum,viewid from
(
Select viewid
from all_stats
order by viewid
)
where rownum between 10 and 20

down't return any rows

and


select rownum,viewid from
(
Select viewid
from all_stats
order by viewid
)
where rownum < 20

returns 19 rows

so how to get from 10 to 20?

To have records from 10 to 20
how about
select * from emp where rownum < 21
minus
select * from emp where rownum < 11

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
from emp)
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.