Re: number of rows returned

Thanks Mike, I had already tried your suggestion, but it does not work. It seems you can not have an order by statement in a subselect. Maybe I did something wrong. I have a friend working for a company which has official Oracle support. I asked him the favour of asking them. Oracle official support (in Spain) says that the only way to do it is with a cursor. They mean I have to select _all_ the rows (10 million ?) and then just use the first four. I am trying to search the knowledge data base of the oracle developers forum, but it does not work. Pedro. ----- Original Message ----- From: "Mike Hanney" <mike@tradebasics.com> To: "Pedro Garre" <pgarre@gesporte.com>; "php-db" <php-db@lists.php.net> Sent: Wednesday, August 02, 2000 2:08 PM Subject: Re: [PHP-DB] number of rows returned > Hi, > > You might be able to put the order in a sub select. > > undesired: > > select first_name > from staff_tbl > where rownum <= 4 > order by first_name desc; > > desired: > > select first_name > from (select first_name > from staff_tbl > order by first_name desc) > where rownum <= 4; > > Not sure about returning rows 4 to 6 though as Oracle will stop retrieving > records if you try rownum => 4 and rownum <= 6, stops at row 4, I think. > > Amazes me too that there is no LIMIT funtion in Oracle. > > Mike. > > > > At 12:59 02/08/00 +0200, Pedro Garre wrote: > >Hi, > > > >I want to get only the first 4 rows from a query. The query is complex, and > >has an order by statement. > > > >Oracle documentation states that ROWNUM is assigned before rows are sorted, > >so I understand that this will _not_ work as desired: > >select ... > >from ... > >where ... > > and ROWNUM < 4 > >order by ... > > > >In Postgresql you just need to include "LIMIT 4". > > > >I also want to retrieve rows 4 to 6, but no clue how to do it. > >In Postgresql is as easy as "LIMIT 4,3" (or something similar). > > > >Anybody knows how to do that with Oracle (the most expensive database in the > >world) ? > > > >Thanks in advance, > > > >Pedro. > > > > > > > > > >-- > >PHP Database Mailing List (http://www.php.net/) > >To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > >For additional commands, e-mail: php-db-help@lists.php.net > >To contact the list administrators, e-mail: php-list-admin@lists.php.net > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > For additional commands, e-mail: php-db-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net > >