USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(PrjID int, EmpID int, [Date] datetime, PrjName char(2))
GO

INSERT INTO myTable99(PrjID, EmpID, [Date], PrjName)
SELECT 1, 1, '10/01/2004', 'P1' UNION ALL
SELECT 2, 2, '08/02/2004', 'P2' UNION ALL
SELECT 3, 2, '11/12/2004', 'P3' UNION ALL
SELECT 4, 2, '05/05/2004', 'P5' UNION ALL
SELECT 5, 3, '05/30/2004', 'P6' UNION ALL
SELECT 6, 3, '09/10/2004', 'P7'
GO

SELECT * 
  FROM myTable99 o 
 WHERE EXISTS (SELECT * 
		 FROM myTable99 i 
	     GROUP BY EmpId 
	       HAVING MAX(i.[Date]) = o.[Date] 
		  AND i.EmpID = o.EmpID)
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

quote:

---

Originally posted by ygeorge

Hi,

My Project table is like this -

PrjID EmpID Date PrjName
1 1 10/01/2004 P1
2 2 08/02/2004 P2
3 2 11/12/2004 P3
4 2 05/05/2004 P5
5 3 05/30/2004 P6
6 3 09/10/2004 P7

......

I need to use one query to return the most recent project for each employee. The return should like the following -

PrjID EmpID Date PrjName
1 1 10/01/2004 P1
4 2 11/12/2004 P5
6 3 09/10/2004 P7

Can anybody help me? Thanks in advance.

George

---