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

Well, yes and no. SQL server "top" clause does not require you to have a "order by" clause - as is the case with the SQL from the OP. Without the "order by", ORACLE ROWNUM indeed has the same effect as SQL server TOP - unless you use TOP <percent>, then it would require a bit more work on the ORACLE side. With "ORDER BY", various analytic functions can be used. Or you can try something like

select * from (select * from t1 order by <column>) where rownum < 11;

• Jusung Yang

"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:
>
> select top 10 item, qty_sold
> from my_table
> order by qty_sold desc
>
> If you tried in oracle
>
> select * from my_table where rownum < 11;
>
> You would get first 10 rows from the table, not top 10, but just the first
> 10 in the table.
> If you try this:
>
> select * from my_table where rownum < 11 order by qty_sold
>
> This still wouldn't help you, because this would first take first 10 rows in
> a table, and then order them.
> This is how you can to this:
>
> 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
> 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 - 19:24:41 CET