Re: rownum

I haven't checked this newsgroup for a while, so I aploogize for the tardy reply.

Here's what I think Chaz is looking for.

Given a LARGE table, how can he get the top 10 values (say salaries). Assuming that the table has an index on the value you want, you can use a hint to force oracle to use it.

     table -> SALARIES
    index -> SALARLY_INDEX

select /*+ INDEX_DESC( SALARIES, SALARY_INDEX ) */ * from SALARIES where rownum < 10 order by salary desc

This will give you the TOP 10 salaries. The bottom 10 salaries are given by:

select /*+ INDEX_ASC( SALARIES, SALARY_INDEX ) */ * from SALARIES where rownum < 10 order by salary asc

At this point, the bottom could also be (but there is no promise in furture versions).:

select /*+ INDEX( SALARIES, SALARY_INDEX ) */ * from SALARIES where rownum < 10 order by salary;

Chaz wrote:

> can anyone help me find a way to limit the number of records returned in a
> select statement? here's what i mean:
>
> in MS SQL Server, the "TOP n" kewords (as in "SELECT TOP 10 fname FROM
> table") specify how many records *out of the resultset generated from the
> rest of the select statement* are actually returned. in Oracle, the closest
> thing I've found is "WHERE rownum <= n". But this rownum is an internal
> index, so it doesn't limit the resultset to the top n of that resultset, but
> to records whose rownum is <= n. Theres a huge difference here, and I'm
> totally stumped.
>
> is there a similar keyword in Oracle?
> do i have to spend the next week writing a stored proc to achieve this?
> any clues?
>
> thx in advance.
Received on Thu Feb 15 2001 - 01:48:34 CET