Re: query - Oops

Sorry..blame it on a momentary case of CRAFT ( Can't remember a freekin' thing )

As usual, Thomas is correct....

John Greco

Thomas Kyte <tkyte_at_us.oracle.com> 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.
>
>the above does not work.
>
>tkyte_at_8.0> create table emp as select * from scott.emp;
>Table created.
>
>tkyte_at_8.0> select sal from emp where rownum < 2 order by sal desc;
>
> SAL
>----------
> 800
>
>tkyte_at_8.0> select max(sal) from emp;
>
> MAX(SAL)
>----------
> 5000
>
>
>
>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
>
>
>In all versions, what would work could be:
>
>
>tkyte_at_8.0> select distinct sal from emp where sal >= ALL ( select sal from emp )
> 2 /
>
> SAL
>----------
> 5000
>
>tkyte_at_8.0>
>
>
>>uday_ind_at_my-deja.com wrote:
>>
>>>Finding the maximum salary from the emp (demo table) without using the
>>>max function
>>>
>>>
>>>Sent via Deja.com http://www.deja.com/
>>>Before you buy.
>>
>>
>>
>> -----------== Posted via Newsfeeds.Com, Uncensored Usenet News ==----------
>> http://www.newsfeeds.com The Largest Usenet Servers in the World!
>>------== Over 73,000 Newsgroups - Including Dedicated Binaries Servers ==-----

  -----------== Posted via Newsfeeds.Com, Uncensored Usenet News ==----------    http://www.newsfeeds.com The Largest Usenet Servers in the World! ------== Over 73,000 Newsgroups - Including Dedicated Binaries Servers ==----- Received on Tue Oct 12 1999 - 13:47:34 CDT