Re: Oracle non-DBA FAQ 0.4

In article <carl.pedersen-270593123649_at_kip-sn-67.dartmouth.edu> carl.pedersen_at_dartmouth.edu (L. Carl Pedersen) writes:
>In article <dtb.738347042_at_otto>, dtb_at_otto.bf.rmit.oz.au (David Bath) wrote:
>>
>>
>> ORACLE NON-DBA FAQ (Frequently Asked Questions) - Version 0.3
>> -----------------------------------------------------------------
>>
 [stuff omitted]
>> 2.14. What is ROWNUM good for ?
 [stuff omitted]
>> The other good thing about ROWNUM is that it is the quickest way to
>> reference a particular row in a table, providing that no table
>> reorganization happens in the meantime.
>
>ACK! This is wrong. You are confusing ROWNUM and ROWID. In fact, since
>ROWNUM is not assigned until after the rest of the WHERE clause is
>executed, the only *single* row you can select using ROWNUM is the first.
>The following will never return any rows, no matter what is in T:
>
> select * from t where rownum = 2;

You may find this to be the most pointless post ever, but ...

The above statement ("the only *single* row you can select using ROWNUM is the first") is false. Consider the following statement:

SELECT ename FROM scott.emp
GROUP BY rownum, ename
HAVING rownum = 2

This will, in fact, pick the second row from the table. If you add a WHERE clause, it would pick the second row from the filtered data. The usefulness of this is dubious, at best, but worth pointing out, since it seems that people often forget about the HAVING clause.

Mark

PS. And no, in case you are wondering, you cannot do an ORDER BY and have it reflected in ROWNUM, even using the GROUP BY ... HAVING paradigm. Received on Thu May 27 1993 - 21:20:28 CEST