help with query

[kodaksmile]

I am trying to select values from a table that has multiple records for each employee -- what I want to do is just grab the record that has the most recent effective date. Below is what I have tried but, it only returns 1 record which is the employee with the most recent effective date but, I need 1 record for each employee....

select ejheeid, ejhhourlypayrate, ejhannsalary
from emphJob
where ejhjobeffdate=(select max(x.ejhjobeffdate) from emphjob as x)

Any suggestions????

Thanks for your help!!!

 

[JayKusch]

Maybe this ...

select ejheeid, ejhhourlypayrate, ejhannsalary, Max(ejhjobeffdate)
from emphJob
group by ejheeid, ejhhourlypayrate, ejhannsalary

Thanks

J. Kusch

 

[kodaksmile]

I tried that but, it returned all records for each person anyway (almost as if it ignored the max on the effective date)

 

[ClaireHCM]

select a.* from MyTable a inner join
(select employee,max(ejhjobeffdate) as MaxDate from MyTable group by employee) staging b
on a.Employee = b.Employee and a.ejhjobeffdate = b.MaxDate

I dont really understand ur column naming convention.But you definately can read by concept!

 

[vc123]

select ejheeid, ejhhourlypayrate, ejhannsalary
from emphJob t1
where ejhjobeffdate=(select max(ejhjobeffdate) from emphJob where ejheeid=t1.ejheeid)

VC

 

[SonOfEmidec1100]

For what its worth I dont see whats wrong with JayKusch's original solution, its what the query builder gives you.

 

[SonOfEmidec1100]

Ignore my last comment - Now I do.

 

[kodaksmile]

THanks all I did get it late last night -- I just added the
y.ejheeid=x.ejheeid into the where clause of the subselect

select ejheeid, ejhhourlypayrate, ejhannsalary
from emphJob
where ejhjobeffdate=(select max(x.ejhjobeffdate) from emphjob as x where y.ejheeid=x.ejheeid)

Thanks -- you guys are the best