Re: How to use 'Top' in oracle?

The pure SQL variant pre 8.1 would be:

select p1.id , etc.... from products p1 where 3 > (

        select count(*)
        from products p2
        where p2.price >= p1.price
        )

i.e. count the number of rows where the
price is greater than the row I am looking at, and if it is only 1 or 2, then this row must be in the top three.

NB. This will probably not perform well on large data sets, and you may want to
change it to count(distinct p2.price) to affect the way that ties for the first
three places are reported.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

NetComrade wrote in message <37ed628d.26437414_at_news.earthlink.net>...


>select product_name from product order by price where rownum<=3 in 8i

>only. Otherwise you'll have to write a Pl/Sql block/proc with a

>cursor.

>On Mon, 25 Sep 2000 17:54:38 +0800, "mhwang" <mhwang_at_sz.utl.com.hk>

>wrote:

>

>>I want to get the first 3 products order by these prices.

>>But i get an error when using : "Select top 3 product_name from product

>>order by product_price".

>>How can i do it ?

>>

>>Any suggestions are appriciated.

>>Ming at 9.25

>>

>>

>>

>