Results 1 to 4 of 4

Thread: Grouping

  1. #1
    Join Date
    Mar 2004
    Posts
    51

    Question Unanswered: Grouping

    Hi,

    I tried many times but I couldn't come up with a query that will yield records of the most recent date for each ID.

    The starting table TBL_DATE that I will use in the query, for example:
    ___________
    ID | Date
    ------------
    01 | 12/01
    01 | 12/05
    02 | 12/08
    03 | 12/10
    03 | 12/11
    03 | 12/16
    ___________

    The desired recordset returned (most recent date for each ID):

    ___________
    ID | Date
    ------------
    01 | 12/05
    02 | 12/08
    03 | 12/16
    ___________


    I tried different techniques using GROUP BY and MAX(), but nothing worked so far... Any ideas?

    Thanks,

    NB

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    This is one way...
    Code:
    select id
    ,        date
    from  (
              select id
               ,        date
               ,        rank() over (partition by id order by date desc nulls last) rank
               from  tbl_date
             ) ranked_dates
    where rank = 1
    ;

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    select distinct(ID), DATE_COL from TBL_DATE A
    where DATE_COL in
    (select max(DATE_COL) from TBL_DATE B
    where A.ID = B.ID)
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Mar 2004
    Posts
    51
    Thanks guys, I'll try it out!

Posting Permissions

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