Home Page for the TeradataForum
 

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023