Re: HELP: SELECT TOP 10

8i allows the order by in inline views. Try Group by.

Group by is a cheat because it's not really designed to sort the rows. Note that it will always do a binary sort so if you need some other order it can't be done (me thinks). The only limitation of group by is that you can't do descending of characters strings without using the translation function to flip it around. Gets a bit messy but can be done.

select a.*
from (select name,..... from person group by name ,.......) a where rownum < 11

Harald Mika wrote:

> Thanks,
>
> Unfortunatly Oracle does not accept an order by statement in inline view.
>
> 2. Unfortunatly it is still not working. If you ever tryed this, can you please
> send me a copy of your code. Maybe I'm missing something here. What optimizer
> mode you have set for your DB? I'm using Oracle 8.0.5 on an NT Box.
>
> Harald
>
> Sybrand Bakker wrote:
>
> > Hi Harald,
> > You could try to use inline views (and a simply don't know whether this will
> > work I didn't try it yet): like this
> > select a.*
> > from (select name from person order by name desc) a
> > where rownum < 11
> >
> > 2
> > If you copied this from your statement, the + sign should directly follow
> > the comment sign, like this /*+, and there should be a space only between
> > the table name and the index name.
> > Also note, if you use a table alias, you must use the table alias instead of
> > the tablename in your hints, or it will not work.
> >
> > Hth,
> >
> > Sybrand Bakker, Oracle DBA
> >
> > Harald Mika wrote in message <3717F768.C0DEFBDF_at_Mika.de>...
> > >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!
> > >
> > >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 Mon Apr 19 1999 - 03:22:53 CDT