From: kennedy-downwithspammersfamily@attbi.net   
      
   Also if sal is indexed then Oracle can use the index to find the max sal.   
   Jim   
   "Romeo Olympia"  wrote in message   
   news:42fc55dc.0405281038.4fa5be5d@posting.google.com...   
   > Actually, since Oracle 8.1.6, this query and other more complicated   
   > ones can be answered by analytic functions.   
   >   
   > select *   
   > from emp   
   > where sal = (select max(sal) from emp)   
   >   
   > can be written as   
   >   
   > select *   
   > from   
   > (select a.*, rank() over (order by sal desc) as rnk   
   >  from emp a   
   > )   
   > where rnk = 1   
   >   
   > This might appear more convoluted than your traditional approach. But   
   > I find it easier to rewrite when you need to vary the original   
   > business question. Like you can get Top-n and not just Top-1.   
   >   
   > afkar@mail2world.com (Bilal Sallakh) wrote in message   
   news:...   
   > > Under the following schema   
   > >   
   > > emp = (empNO, sal)   
   > >   
   > > To get the employee with the largest salary we do:   
   > >   
   > > SELECT   empNO, sal   
   > > FROM     emp   
   > > WHERE sal = (SELECT MAX(sal) FROM emp)   
   > >   
   > > Right?   
   > >   
   > > So we calculate the maximum salary first, then we find the employees   
   > > that achive that maximum.   
   > >   
   > > Suppose that there is one employee that achieves the maximum.   
   > >   
   > > Why can't ORACLE find the desired empNO directly?   
   > >   
   > > I suppose something like:   
   > >   
   > > SELECT empNO that corresponds to the max, Max(sal)   
   > > FROM emp   
   > >   
   > > For sure this is wrong becuase the select list contains only   
   > > aggregated or grouped expressions   
   > >   
   > > As far as I know, to get the maximum salary, Oracle scans the rows one   
   > > by one, keeping the maximum salary in a "variable" to return it. So   
   > > ORACLE knows the record which caused updating the "variable". Why   
   > > can't we exploit this to get other values in the row that achieved   
   > > that maximum?   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)