Re: Sorting problems oracle 9i

"csharpgeek" <cepcorreia_at_hotmail.com> a écrit dans le message de news:1104328354.507075.291850_at_f14g2000cwb.googlegroups.com...
| 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
|

Maybe if you read the doc or search on Google, you found that, in this case, Oracle get the 3 rows and then orders them. And maybe you saw the solution is:

select * from (select ... order by ...) where rownum<=3;

Regards
Michel Cadot Received on Wed Dec 29 2004 - 08:45:52 CST