Re: Sorting problems oracle 9i

csharpgeek wrote:

> Hi,
>
> I've created a pipelined oracle stored proc which returns 3 rows from a
> 25 mil+ table. I need to sort on a price field, and limit the result
> set using ronum the query would be simply as follows.
>
> SELECT ITEM,PRICE FROM TBL WHERE ROWNUM <= 3 ORDER BY PRICE ASC;
> SELECT ITEM,PRICE FROM TBL WHERE ROWNUM <= 3 ORDER BY PRICE DESC;
>
> the queries return exacly thesame result, ie
>
> ITEM PRICE
> ---- ----------
> A 499
> B 499
> C 499
>
> This is incorrect because when i execute
>
> SELECT ITEM,PRICE FROM TBL WHERE INITIALS = 'AAA' AND TYPE = 4 AND
> ROWNUM <= 3 AND PRICE > 499 ORDER BY PRICE ASC;
>
> i get
>
> ITEM PRICE
> ---- ----------
> G 699
> H 799
> J 899
>
> I've tried using the following example
>
> SELECT ITEM,PRICE FROM TBL,dual b WHERE PRICE =
> decode(b.dummy(+),'X',NULL,NULL) AND ROWNUM <= 3 ORDER BY PRICE;
>
> but it is rather expensive.
>
> The core of me problem is the following retrieve 3 rows with the
> highest price from a 25 mill. row table.
>
> thx,
> carlos

Take a look at RANK.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)