iS THERE A top(X) FUNCTION IN oRACLE?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: iS THERE A top(X) FUNCTION IN oRACLE?

Hybrid View

  1. #1
    Join Date
    Sep 2000
    Posts
    78

    Arrow

    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!

  2. #2
    Join Date
    Mar 2000
    Location
    india
    Posts
    54
    Hi,
    I dont think there is any function to give top records,basically you have to write a query.

  3. #3
    Join Date
    Oct 2000
    Location
    Cambridge, MA (Boston)
    Posts
    144
    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.


  4. #4
    Join Date
    Jun 2000
    Posts
    417
    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.

  5. #5
    Join Date
    Jun 2000
    Posts
    117

    Talking Top N syntax

    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

  6. #6
    Join Date
    Sep 2000
    Posts
    78
    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?

  7. #7
    Join Date
    Sep 2000
    Posts
    23

    Question

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

  8. #8
    Join Date
    Mar 2000
    Location
    westboro, ma, USA
    Posts
    14
    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.


    Rajesh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width