Re: Is there anything in oracle similar to top in sql server 2000?

From: D.Y. <dyou98_at_aol.com>
Date: 27 Oct 2002 12:01:16 -0800
Message-ID: <f369a0eb.0210271201.251e7613_at_posting.google.com>


"emerald" <emerald_at_net.hr> wrote in message news:<apehh0$mh3$1_at_sunce.iskon.hr>...
> > Typically you use rownum. For example,
> >
> > select * from t1 where rownum < 11;
> >
> > That's the same as the SQL server top 10.
> >
> > - Jusung Yang
> >
>
> No it isn't. In SQL Server if you want to list, say, top 10 items by
> qty_sold, you would do this:

Looks like the original poster just wants to return the first n rows regardless of the order, in which case rownum<11 will be equivalent to SQL Server's top 10 without an order by.

>
> select top 10 item, qty_sold
> from my_table
> order by qty_sold desc

 ...
>
> select A.item, A.qty_sold, A.rank_field
> from (select item, qty_sold, rank() over (order by qty_sold desc) from
> my_table) A
> where A.rank_field<=10

Instead of this, you can use rownum<=10 to account for ties.

> order by A.rank_field;
>
> The inner query (select item, qty_sold, rank() over (order by qty_sold desc)
> from my_table) uses rank function to asign each field it's rank. The outer
> query then takes top 10.
>
> Check chapter 17 in Data Warehousing Guide for more details on rank
> function.
> This kind of query can't be used in pl/sql directly. If you need to use it
> anyway, use it with execute immediately.
Received on Sun Oct 27 2002 - 21:01:16 CET

Original text of this message