Help! Should be a simple query

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 2005 Forums

Transact-SQL (2005)

Help! Should be a simple query

Author

Topic

robinsonpr
Starting Member

4 Posts

Posted - 2008-03-19 : 06:55:49

Hi I've got what should be a simple query but I'm not sure how to write it.

I have a table which holds employee assignments to positions, which holds historical data.

Eg:


ID   EmployeeID   StartDate     PositionID
---  ----------   ---------     ----------
1    7663         23/1/2007     234
2    7663         28/6/2007     272
3    7663         1/02/2008     983
4    81212        2/03/2008     2888

Now, what I'm after for each employee is their current position based on the highest "StartDate" for that employee.

So from the above table I want the results:


3    7663         1/02/2008     983
4    81212        2/03/2008     2888

Can anyone help? Do I need a Group By? That clause always confuses me!!!

Thanks!!!!

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-03-19 : 06:58:59

SELECT T1.*
FROM tbl T1
INNER JOIN ( SELECT EmployeeId, MAX(StartDate) AS StartDate FROM tbl
GROUP BY EmployeeId) T2 ON T1.EmployeeId = T2.EmployeeId
AND T1.StartDate = T2.StartDate

robinsonpr
Starting Member

4 Posts

Posted - 2008-03-19 : 07:00:54

Wow that was quick. I think I understand what it's doing too which is a bonus! Many thanks for the help!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-19 : 07:12:20

Or do it using SQL 2005 function

SELECT t.ID,t.EmployeeID,
t.StartDate,
t.PositionID

FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY StartDate DESC) AS RowNo,
ID,EmployeeID,
StartDate,
PositionID
FROM EmployeeAssignments
)t
WHERE t.RowNo=1