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)
No comments:
Post a Comment