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

> 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 Sat Oct 26 2002 - 18:57:16 CEST