Re: number of rows returned

Hi Pedro, I was about to suggest using a cursor to you when your mail popped into my inbox :o) I actually use Informix and think exactly the same thing as you - How come some of the most expensive DB's have such a lacking feature! I suppose many MySQL people feel the same way about sub selects(which I believe is/has been addressed). If you do mannage to find out, or anyone on the list knows a better way, then please share it with the rest of us. I will have a serious need for this in the near future so may have another bash myself :-) Regards, - Paul - ----- Original Message ----- From: "Pedro Garre" <pedro@siliconartists.com> To: "php-db" <php-db@lists.php.net> Sent: Wednesday, August 02, 2000 2:21 PM Subject: Re: [PHP-DB] 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 > > > > > > > > -- > 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 > >