Why ORACLE doesn't do it?

[Bilal Sallakh]

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?

[Romeo Olympia]

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.

af***@mail2world.com (Bilal Sallakh) wrote in message news:<d5**************************@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?

[Jim Kennedy]

Also if sal is indexed then Oracle can use the index to find the max sal.
Jim
"Romeo Olympia" <ro******@hotmail.com> wrote in message
news:42**************************@posting.google.c om...

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.

af***@mail2world.com (Bilal Sallakh) wrote in message

news:<d5**************************@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?