CHILD functions?

How can I pick out a "top child" in SQL Server 2000?

Example:

----------
| Employee || code || action date |
----------
| 00000001 || AAA || 01/02/2005 |
| 00000001 || DDD || 01/02/2001 |
| 00000001 || CCC || 01/06/2000 |
| 00000002 || AAA || 01/02/2006 |
| 00000002 || CCC || 01/10/2004 |
| 00000003 || DDD || 05/12/2001 |
| 00000004 || AAA || 09/09/2006 |
| 00000004 || SSS || 09/06/2000 |
----------

I want to pick out the record with the latest date for every employee so I end up with something like this:

----------
| Employee || code || action date |
----------
| 00000001 || AAA || 01/02/2005 |
| 00000002 || AAA || 01/02/2006 |
| 00000003 || DDD || 05/12/2001 |
| 00000004 || AAA || 09/09/2006 |
----------

Any ideas?
Thanks in advance!

-GeorgeV??:
SELECT Employee, code, MAX([action date])
FROM YourTable
GROUP BY Employee, code|||Hmm, nice idea but it doesn't want to work.

Any more suggestions?

-GeorgeV|||SELECT Employee, code, [action date]
FROM YourTable YT
WHERE [action date] = (SELECT MAX([action date])
FROM YourTable
WHERE Employee = YT.Employee)

Check This

DECLARE @.Temp int
DECLARE @.FullQry varchar(50)

set @.FullQry='select @.Temp=Emp_ID from Employee where....'
Exec(@.FullQry)
select @.@.ROWCOUNT

My Employee table has 3 records and this query sholud return me @.@.ROWCOUNT=1
but it will return 0 why this i am not able to find out.Exec function return ROWCOUNT or not?The domain of the selection "@.Temp=" is outside the domain of the executed statement so the query as you have written it will return:

Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Temp'.

(1 row(s) affected)

The correct way to return values from a dynamically executed SQL section is to use sp_executesql with output semantics:

DECLARE @.Temp int
DECLARE @.Rows int
DECLARE @.sql nvarchar(4000)

SET @.sql=N'SELECT @.Rows=@.@.ROWCOUNT, @.Temp=EMp_ID FROM ...'

EXEC sp_executesql @.sql, N'@.Temp int OUTPUT, @.Rows int OUTPUT', @.Temp OUTPUT, @.Rows OUTPUT

SELECT @.Rows, @.Temp