Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

help with query

Status
Not open for further replies.
Joined
Jun 19, 2002
Messages
294
Location
US
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!!!
 
Maybe this ...

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

Thanks

J. Kusch
 
I tried that but, it returned all records for each person anyway (almost as if it ignored the max on the effective date)
 
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!
 

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

VC
 
For what its worth I dont see whats wrong with JayKusch's original solution, its what the query builder gives you.
 
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 :-)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top