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

dyou98_at_aol.com (D.Y.) wrote in message news:<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.

Okay, just my 2 cents worth...

Assuming the customer table had the columns: cust_no, customer_name, you could:

          select * 
          from
          (
            select cust_no, customer_name
            from customer
            order by cust_no
          )
          where rownum <=10

Would work to get the top ten if you're using 8i. Received on Thu Oct 31 2002 - 05:00:48 CET