Re: SQL Problem

On Wed, 15 Jul 1998 13:18:33 GMT, Markz_at_starnet.lenfest.com wrote:

>However, if your problem is to , for example, to find the 200th
>richest man from a 'fortune_500' table :
>
>fortune_500 :
> name VARCHAR2
> worth NUMBER
>you would
>
>select name from fortune_500
>where ROWNUM = 200
>ORDER BY worth;
>
>Note ROWNUM, a dummy column representing the
>ordinal of the row AND that I am ordering by worth.
>ORDER BY is important as it eliminates the randomness
>to which Mr. Larsen (correctly) alludes.
>

I'm sorry, but this query wouldn't select the 200th richest man from fortune_500.

The query wouldn't produce any result, because as each row is selected from the table, it is made a test to see if it is the 200th row and since it never is, then no row meets the condition in the WHERE clause. Thus no result is given.

Another point - you can't use ROWNUM in conjunction with ORDER BY to get the rows which contain the top values of a column (WHERE rownum <= 200, e.g.), because rows are randomly selected up to the value specified in ROWNUM and only then the ORDER BY is applied (and it is applied only to the rows selected) - which produces a random set of 200 rows, not the rows which contain the top 200 values of a column.

Nuno Guerreiro Received on Wed Jul 15 1998 - 11:59:04 CDT