Re: query

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 ==-----

--
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 Received on Tue Oct 12 1999 - 11:37:01 CDT