Re: How to retrieve top 10 rows

From: Mark G <markag_at_wonderstats.com>
Date: Thu, 17 Aug 2000 07:57:33 -0400
Message-ID: <F4155D9F9AA4D111B9990020AFBA52D53F49C8@class06.ip061.sfi-software.com>

Nope. That won't work consistently as rows are added if you want to do any ORDER BY's because ROWNUM is done prior to sorting.

You need to do a Top-N query like this:

select ROWNUM AS Rank, Name, Region, Sales from   (select Name, Region, sum(Sales) AS Sales

      from Sales GROUP BY Name, Region
      order by sum(Sales) DESC)

WHERE ROWNUM <= 10

The inner query is executed and ordered prior to the outer query restricting the number of rows.

"Rui Anastácio" <ranastacio_at_mail.telepac.pt> wrote in message news:399b9a95.85249522_at_news.telepac.pt...
> On Thu, 17 Aug 2000 13:01:46 +1000, "Yulia" <yuliam_at_catuity.com>
> wrote:
>
> >I do not know how to limit the number of rows
> >returned by a query.
> >In Sybase I could use "set rowcount 10".
> >
> >What does the same trick in Oracle?
> >
> >Yulia
> >
> >
>
> Use rownum like this:
>
> SELECT *
> FROM TABLE
> WHERE rownum <= 10
>
> Rui Anastácio
Received on Thu Aug 17 2000 - 06:57:33 CDT