Re: Limitting number of rows returned from a select.

On Mon, 17 Jun 1996 17:31:22 -0700, Robert Cordingley <cord_at_zoom.com> wrote:

>> select * from <table_name>
>> where ROWNUM < 200 .
>>
>> This will return the first 200 rows.
>> I'm pretty sure this functionality is supported in all versions subsequent to 7.0 (including 7.0).
>> I think v6 also supports this.
>>
>> However your customer needs to keep in mind that conditions testing for ROWNUM values greater than 1 are
>> always false and will not return any rows.
>
>Also bear in mind that the 200 rows returned will be the first 200
>*selected*. This is prior to any order by clauses. If there is an index and
>the optimizer chooses the index, then they will appear in index order. If
>there's no index or the optimizer chooses to ignore the index, then they'll
>appear in "storage" order. Strange things can happen with rownum, but it's
>great for guaranteeing a single row return (like when some putz with the
>system password inserts a row into dual). You can do something like "select
>user from sys.dual where rownum = 1" to guarantee that you'll get what you're
>expecting.
>

What about the putz that issues "delete from dual; COMMIT;" :)

Not that is has ever happened in my database, nope, no siree.... :)

Been bitten by that more then one row in dual as well.

Actually, inline views and group bys can be pretty useful with rownum. Consider the following:

select *
from ( select deptno, sum(sal) sal

         from emp
        group by deptno )

Will get the first two rows from the inline view (which since you grouped by deptno is nicely sorted by deptno for you). By pushing the group by into the inline view, it will be done before the rownum is set and hence rownum won't affect the group by, for example, the first query following returns the first two depts with their SAL's summed up. The other query returns the first two rows from the EMP table, with their sals summed up. The first query uses all rows to create the dept/sum(sal) view, the second query uses the first two ROWS from the emp table to do the same.

SQL> select * from
  2 (select deptno, sum(sal) sal from emp   3 group by deptno )
  4 where rownum < 3
  5 /  

    DEPTNO SAL
---------- ----------

        10    9895.86
        20     8032.5
 

SQL> select deptno, sum(sal) sal from emp   2 where rownum < 3
  3 group by deptno
  4 /  

    DEPTNO SAL
---------- ----------

        10       3151
        60       3420



>					Robert

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Fri Jun 21 1996 - 00:00:00 CEST