Problem to get record with Max (UpdatedDate)

[adasoft]

Hi, I have written following Query

( Select PersonnelNumber , dtEmpAccount.EmployeeID ,AccountID, AttributeCode, CurrentValue, UpdatedDate,AttributeUnit , recordType
FROM dtEmpAccount, Employee, dtAccountDef Where Employee.EmployeeID = dtEmpAccount.EmployeeID
AND dtEmpAccount.AccountID=dtAccountDef.AttributeID AND UpdatedDate >= {d '2005-01-01'} AND Recordtype in( 'TP','PS','DR')
and AccountID in (Select dtAccountDef.AttributeID from dtAccountDef Where AttributeCode in ('AC4001','AC4002' ) )
and dtEmpAccount.EmployeeID=1
)
This query return me 4 record with didderent date.
I want to get out one with Max updated date for each AccountID

Can any one help..

 

[JayKusch]

Just a shot ...

( Select  PersonnelNumber , dtEmpAccount.EmployeeID ,AccountID, AttributeCode, CurrentValue, MAX(UpdatedDate),AttributeUnit , recordType
FROM dtEmpAccount, Employee, dtAccountDef   Where Employee.EmployeeID = dtEmpAccount.EmployeeID
AND dtEmpAccount.AccountID=dtAccountDef.AttributeID AND  UpdatedDate >= {d '2005-01-01'}  AND Recordtype in( 'TP','PS','DR') 
and   AccountID in  (Select dtAccountDef.AttributeID from dtAccountDef  Where AttributeCode in ('AC4001','AC4002'  ) )  
 and  dtEmpAccount.EmployeeID=1 
group by PersonnelNumber , 
        dtEmpAccount.EmployeeID ,AccountID, AttributeCode, CurrentValue, AttributeUnit , recordType)

Thanks

J. Kusch