Re: query

On Tue, 12 Oct 1999 12:37:01 -0400, you wrote:

>A copy of this was sent to johng_at_mm.com (TurkBear)
>(if that email address didn't require changing)
>On Tue, 12 Oct 1999 16:21:02 GMT, you wrote:
>
>>Try using the following ( its a bit of a kludge, but it seems to work)
>>
>>Select salary from emp
>>where rownum < 2
>>order by salary desc;
>>
>>( it makes use of the fact that the rownum pseudocolumn is assigned after the
>>sort is done, so 1 record is returned, the top salary... )
>>
>
>back up.
>
>the rownum pseudocolumn is assigned BEFORE the sort it done in the above. It is
>'lucky' if you are getting the top salary.
>

[snip]

>
>In Oracle8i, release 8.1 you can use the order by in an inline view to do the
>above, it would look more like:
>
> select * from ( select sal, rownum r from emp order by sal desc ) where r < 2
>

oops -- should have been:

select * from ( select sal from emp order by sal desc ) where rownum < 2

the above query I had (and didn't actually type into sqlplus) suffers from the same problem that rownum is assigned BEFORE the order by. The inline view causes the order by to happen before the assignment of rownum.

[snip]

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation