Analytic functions? 2

[sem]

I need to select for each department a salary of a person with the longest record of service. I suspect that analytic functions (FIRST_VALUE?) may be used in some way but can't grasp how.
The real task is more complex and I must avoid accessing "EMP" table more then once per "DEPT" row.

Regards, Dima

http://seminihin.narod.ru

 

[Dagon]

Couldn't you just do something like this ?

select emp_name, sal, deptname, start_date
from
(select e.emp_name, e.sal, d.deptname, e.start_date,
rank() over (partition by e.dept_id order by trunc(sysdate) - e.start_date desc) as serving_rank
from emp e, dept d
where d.dept_id = e.dept_id)
where serving_rank = 1

 

[sem]

Thank you for suggestion, but again, the goal is not to get results anyway and not to test analytic functions but rather to access child table less times.
In your example for each department ALL employees are sorted. My requirement is to get only the first one. Actually I have an index on emp(dept_id,start_date) thus using it I want to get only the first row.

BTW what was the idea of order by trunc(sysdate) - e.start_date desc? Why not just order by e.start_date?

Regards, Dima

http://seminihin.narod.ru

 

[stefanhei]

Dima, a merge join with pushed rank will be the best you'll get. In Dagon's solution you merge first, then push. I was about to post this:

select dname,e.ename,sal,hiredate
  from (select row_number() over(partition by deptno order by hiredate asc) rn,
	           deptno, ename,sal,hiredate
          from scott.emp) e,
       scott.dept d
 where e.deptno = d.deptno
   and rn = 1;

Here you push first, then merge, but as both solutions avoid nested loops the difference should be minimal.

@Dagon: seems that both of us like analytic functions ;-)

Stefan

 

[sem]

Ok, thank you both. I realized that Oracle is clever enough to push rank (row_number) condition as as "stop key"

Regards, Dima

http://seminihin.narod.ru