Re: HELP: SELECT TOP 10

Selecting the 'top 10' from a list in Oracle is one of those perenial problems
that Oracle doesn't handle too well (IMHO).

In Oracle 8 and greater you can do this:

SQL> select name
 2 from ( select name from persons where city >'NEW YORK' order by name )  3 where rownum <= 10;

Prior to Oracle 8 you cannot order a subquery, so you need to use this:

SQL> select a.name
 2 from persons a
 3 where a.city > 'NEW YORK'
 4 and 10 > ( select count(*) from persons b where b.city = a.city and b.name > a.name );

The second example is messy, and very database intensive, so if you can narrow the search criteria and/or use indexing that would be enourmously helpful. Alternatively, create a PL/SQL procedure and use a cursor.

HTH Graham

Harald Mika wrote in message <37188C51.653D6D46_at_Mika.de>...
>Hi Hermant,
>
>thanks for you quick response. But your suggestion won't work necessarily.
>The thing is, that the ROWNUM<11 condition is checked before the ORDER BY.
>For example:
>
>1. You have an index on CITY
>2. Do the following query:
>
> SELECT NAME FROM PERSONS
> WHERE ROWNUM < 11
> AND CITY>'NEW YORK'
> ORDER BY NAME
> /
>
>3. What will happen:
>- The optimizer will choose the index on CITY to access the table persons.
>- Oracle will take the first ten records
>- Oracle will sort them by the name
>
>4. That's leads me to my next question: How to force Oracle to use an
index?
>
>
>
>Hemant Chitale wrote:
>
>> Harald Mika wrote:
>>
>> > Two very urgent questions:
>> >
>> > 1. Is there a way to perform a query like
>> > SELECT TOP 10 NAME ..... FROM PERSONS ORDER BY NAME
>> > which will generate only the first 10 records. There is something like
>> > this in MS Access. I'm sorry to mention this!
>>
>> Use ROWNUM.
>> SELECT NAME FROM PERSONS
>> WHERE ROWNUM < 11
>> ORDER BY NAME
>> /
>>
>> >
>> >
>> > 2. How do I manipulate the execution plan of query. I want to force
>> > Oracle to use a specific index. I tried the hint /* + INDEX (PERSONS,
>> > PERSON03) */ but Oracle does not seem to recognize this.
>> >
>> > Any help is appreciated. It would be great if it was before Sunday!!!
>> >
>> > Thanks
>> > Harald
>
Received on Tue Apr 20 1999 - 13:04:28 CDT