Re: number of rows returned

Hi Pedro, Order by in sub selects does not work in releases prior to Oracle 8.1. Here is a work around example. I expect you've tried it allready. Also, it's probably even slower than using a cursor of 10 million rows! SELECT first_name FROM staff_tbl a WHERE 4 >= (SELECT COUNT(DISTINCT first_name)

            FROM staff_tbl b
            WHERE b.first_name >= a.first_name)

ORDER BY first_name DESC; Looks like a cursor is the only way to get your rows 4 to 6. Mike. P.S. Apologies to the list for talking more SQL than PHP. At 15:21 02/08/00 +0200, 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. ----- 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