Returning largest value for each user

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Returning largest value for each user

Author

Topic

Huntin_Answers
Starting Member

1 Post

Posted - 2004-08-18 : 07:59:55

I'm fairly new to the SQL game, so sorry if this is trivial....

In my table, EMPHIST, i'd like my query to return only the latest record for each employee. For Example:

EmpID StartDte EndDte Position
1 1/1/2000 2/1/2000 CLERK
1 2/2/2000 3/1/2000 ASST MGR
2 1/1/2000 2/1/2000 CLERK
2 2/2/2000 3/1/2000 NIGHT MGR

I'd like to return the EMPs with their last position held:

EmpID POSITION
1 ASST MGR
2 NIGHT MGR

Any help is greatly appreciated!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-18 : 08:17:43

this should do it:

select t1.*
from EmpHist t1
inner join (select EmpID, Max(StartDte) as StartDte from EmpHist group by empID) t2
on (t1.EmpId = t2.EmpId) and (t1.StartDte = t2.StartDte)

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources