Query help? - SQL Server Forums

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

SQL Server Development (2000)

Query help?

Author

Topic

Petehe
Starting Member

20 Posts

Posted - 2004-04-27 : 03:54:19

I have a table like
CustomerID, EmployeeID, EmployStartDate
1, 1, 1/1/2003
1, 2, 1/4/2004
2, 3, 1/1/2003
2, 4, 1/5/2005

Could u design a query to show which one is most recent employed for each company?

Thanks

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-27 : 04:00:12

SELECT TOP 1 WITH TIES CustomerID, EmployeeID, EmployStartDate
FROM TableName
ORDER BY EmployStartDate DESC

--**** WITH Ties brings back more than 1 if the latest employstartdates are equal

Duane.

Petehe
Starting Member

20 Posts

Posted - 2004-04-27 : 22:43:53

Thanks for your help.

But I want something will show me the most recent employee of
each company?
Something like
1, 2, 1/4/2004
2, 4, 1/5/2005

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-27 : 23:02:27

SELECT
e1.CustomerID,
e1.EmployeeID,
e1.EmployeeStartDate
FROM
Employee e1
INNER JOIN (
SELECT CustomerID, MAX(EmployeeStartDate) AS EmployeeStartDate
FROM Employee) e2 ON e1.CustomerID = e2.CustomerID
AND e1.EmployeeStartDate = e2.EmployeeStartDate

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Petehe
Starting Member

20 Posts

Posted - 2004-04-27 : 23:20:48

Thank you so much, that's what I want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources