Re: number of rows returned

Pedro Garre wrote: > > 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, a preliminary Google search suggests to me that Oracle, like Sybase my familiar SQL dialect, will do a SELECT TOP <n>. My guess you could do this without needing to do the subselect, and just have the ORDER BY at the end of the query and be able to pick of the first or last (depending on ORDER BY) results. My guess is that your query would look like this: SELECT TOP 4 first_name FROM staff_tbl ORDER BY first_name DESC; Just my $0.02 Steven Campbell > 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