Re: SQL question with ROWNUM

Thanks for reply, but I need top 10 records after order by clause is met, so can't use where clause for rownum before order by, thats why I use nested select but still not working. thanks anyway.

"Happy" <allan_at_livvy80.freeserve.co.uk> wrote in message news:969o0k$mc0$1_at_newsg4.svr.pol.co.uk...
> ORDER BY clause must follow
> WHERE clause
>
> Cheers
> Allan
>
> "kush" <kdhakal_at_eline.com> wrote in message
> news:OHVh6.13481$1%2.710868_at_sjc-read.news.verio.net...
> > hi! I want to get only top ten records after order by clause is met,
 query
> > looks like this:
> > SELECT * FROM (select distinct p.prodid,pr.prodID as
> > reviewed,p.title,p.imagefile,p.priority,p.price,b.qty_sold
> > from bestsellers_twoweeks b, product p, products_reviewed pr
> > where b.selectionid = 3
> > and b.prodid=p.prodid
> > and pr.prodID (+)= p.prodID
> > order by b.qty_sold desc) WHERE ROWNUM <= 10;
> > It gave me results as I expected when I tried to run it in sql editor
 TOAD.
> > But the problem is I need to use it inside a package with ref cursor and
> > when I tried to compile it it gives me error at line number where the
 order
> > by clause is:
> > open r_cursor for
> > select * from (select distinct p.prodid, pr.prodID as reviewed,
> > p.title, p.imagefile, p.priority, p.price,b.qty_sold
> > from bestsellers_twoweeks b, product p, products_reviewed pr
> > where b.selectionid between selbottom and seltop
> > and b.prodid=p.prodid
> > and pr.prodID (+)= p.prodID
> > order by b.qty_sold desc) where rownum <=10;
> >
> > PLS-00103: Encountered the symbol "ORDER" when expecting one of the
> > following:
> >
> > . ( ) * @ % & - + / mod rem with an exponent (**) and or
> > group having intersect minus start union where connect ||
> >
> > Could anyone tell me why I can run this query in sql editor and can't
> > compile same inside package. I am using Oracle 8.1.5.
> > Thanks.
> >
> >
> >
> >
> >
>
>
Received on Mon Feb 12 2001 - 17:00:39 CST