Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2002
    Posts
    456

    Unanswered: DB2 Query Question

    Table Employee:

    ID Name Project_Name Date
    10 A AAA Jan 10, 2005
    10 A AAA Nov 10, 2005
    20 B BBB APR 10, 2005
    20 B BBB DEC 10, 2005

    I want to write a query where only one row is returned for each employee with recent date:

    10 A AAA Nov 10, 2005
    20 B BBB DEC 10, 2005

    Thanks for the help in advance. Paul

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    How about:

    SELECT ID,Name,Project_Name, MAX(Date)
    From Employee
    GROUP BY ID, Name, Project_Name

  3. #3
    Join Date
    Mar 2005
    Posts
    7
    if your table contains more columns or its cardinality is different then your example, you might want to try this

    select * from
    (select
    ID,
    Name,
    Project_Name,
    Date,
    row_number() over (partition by ID order by date desc) as x
    from Employee) T1
    where T1.x = 1

Posting Permissions

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