Why ORACLE doesn't do it?

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 - 06:19:13 CDT