Re: Why ORACLE doesn't do it?

Also if sal is indexed then Oracle can use the index to find the max sal. Jim
"Romeo Olympia" <rolympia_at_hotmail.com> wrote in message news:42fc55dc.0405281038.4fa5be5d_at_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_at_mail2world.com (Bilal Sallakh) wrote in message
news:<d5fb3df3.0405280319.6cdef502_at_posting.google.com>...
> > 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?
Received on Sat May 29 2004 - 01:51:38 CEST