Oracle Analytics / Analytic Function: How to avoid “referencing the table twice in queries having aggregate functions”.

Database : Oracle 10g
Schema : Scott/Tiger
Table : emp

Analytic Functions syntax :
Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <....>] [] )

We come across this very often in our development world.

We need to apply an aggregate function on a column and then get another column based on that aggregate.

Let me give an example.

Say "We need to find out the Names of those employees who earn the max salary for each department".

Immediately, we jump in and write a query similar to this:

select *
from emp
where sal in (select max(e.sal) sal from emp e group by e.deptno);

Even though the table is referenced twice, this query runs very fast on the "emp" table, coz the table is very small.

But, what if your table is having millions and millions of records?

So, now the questions is "how to avoid referencing the table twice?”

Once again Analytic functions rock and roll !!

Here we go:

select * from (
select e.* ,max(sal) over (partition by deptno) max_sal
from emp e)e
where sal = max_sal;

Long Live "Tom Kyte".

Good Luck,
r-a-v-i