Re: Why ORACLE doesn't do it?

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 Fri May 28 2004 - 20:38:45 CEST