Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: 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

  2. #2
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    ??:
    Code:
    SELECT Employee, code, MAX([action date])
    FROM YourTable
    GROUP BY Employee, code

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hmm, nice idea but it doesn't want to work.

    Any more suggestions?

    -GeorgeV

  4. #4
    Join Date
    Nov 2002
    Posts
    272
    Code:
    SELECT Employee, code, [action date]
    FROM YourTable YT
    WHERE [action date] = (SELECT MAX([action date]) 
                           FROM YourTable 
                           WHERE Employee = YT.Employee)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •