Archives of the TeradataForum

Message Posted: Tue, 20 May 2003 @ 10:06:24 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Need help with Query

From:		Dieter N�th

Anomy Anom wrote:

Employee has the following columns

Employee_Number
Name,
Date_Of_Birth
Department
Date_of_last_review

Review_Date has the following columns

Employee_Number
Date_Of_Review
Pay_Rise_Given

I need to find out the pay_rise_given at the last review for each employee, so in effect I need the Maximum date_of_review (On Review_Date) for the employee and need to know the most efficient way of finding the this and matching it to each employee.

If employee.date_of_last_review = max(review_date.date_of_review) then:

select e.name, r.date_of_review, r.pay_raise_given, ...
from employee e join review_date r
on e.employee_number = r.employee_number
and e.date_of_last_review = r.date_of_review

If not, then:

select e.name, r.date_of_review, r.pay_raise_given, ...
from employee e join review_date r
on e.employee_number = r.employee_number
qualify
   rank() over (partition by r.employee_number
                order by r.date_of_review desc) = 1

Maybe it's faster, when you move the rank in a Derived Table

select e.name, r.date_of_review, r.pay_raise_given, ...
from employee e join
   (select * from review_date
    qualify rank() over (partition by r.employee_number
                         order by r.date_of_review desc) = 1) r
on e.employee_number = r.employee_number

A query using RANK (or ROW_NUMBER in V2R5) is probably more efficient than the typical correlated subquery:

select e.name, r.date_of_review, r.pay_raise_given, ...
from employee e join review_date r
on e.employee_number = r.employee_number
where r.date_of_review =
   (select max(date_of_review) from review_date
    where r.employee_number = employee_number
   )

Unless you have millions of employees you probably won't notice big performance differences between those queries.

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference