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