Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    7

    Unanswered: How to retrieve the row with max date on each group.

    The data is store like this

    case-id dept-cd start-dt value
    987001 AB 03/02/2010 501
    987001 AB 02/15/2010 489
    987001 AB 03/12/2010 443
    987001 AB 03/15/2010 490
    560112 AB 01/10/2010 325
    560112 AB 03/17/2010 521
    560112 AB 03/10/2010 600

    For the data above, i need an SQL to retrieve 987001 ,AB ,03/15/2010 ,490 and 560112 , AB , 03/17/2010 ,521.

    For each case-id , the row which has the max date , has to be retrieved. Appreciate your help.

    Thanks
    DB2 begin.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    select A.*
    from table-name A
    where A.start-dt =
    (select max(B.start-dt)
    from table-name B
    where B.case-id = A.case-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

  3. #3
    Join Date
    Jul 2009
    Posts
    17
    One another way to do that....
    select A.*
    from table-name A,(select case_id,dept_cd,max(B.start-dt)
    from table-name B
    group by case_id,dept_cd) B

    where A.start-dt =B.start-dt
    and A.case_id=B.Case_id
    and A.dept-cd=B.dept-cd
    Vyas| Miracle Happens

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Using OLAP specification is another way.

    Code:
    SELECT case_id , dept_cd , start_dt , value
      FROM (SELECT t.*
                 , ROW_NUMBER()
                     OVER(PARTITION BY case_id
                              ORDER BY start_dt DESC) AS rn
              FROM table_name t
           ) t
     WHERE rn = 1
    ;
    1) By using ROW_NUMBER(), you will get one row for each case-id, even if there are multiple rows which have the max date.
    2) By using RANK()(or DENSE_RANK() ), you will get all rows which have the max date.

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    table_name(case_id , dept_cd , start_dt , value) AS (
    VALUES
      (987001 , 'AB' , '03/02/2010' , 501)
    , (987001 , 'AB' , '02/15/2010' , 489)
    , (987001 , 'AB' , '03/12/2010' , 443)
    , (987001 , 'AB' , '03/15/2010' , 490)
    , (560112 , 'AB' , '01/10/2010' , 325)
    , (560112 , 'AB' , '03/17/2010' , 521)
    , (560112 , 'AB' , '03/10/2010' , 600)
    , (987001 , 'AX' , '03/15/2010' , 370)
    , (987001 , 'AY' , '03/14/2010' , 250)
    )
    SELECT case_id , dept_cd , start_dt , value
      FROM (SELECT t.*
                 , RANK()
                     OVER(PARTITION BY case_id
                              ORDER BY start_dt DESC) AS rn
              FROM table_name t
           ) t
     WHERE rn = 1
    ;
    ------------------------------------------------------------------------------
    
    CASE_ID     DEPT_CD START_DT   VALUE      
    ----------- ------- ---------- -----------
         560112 AB      03/17/2010         521
         987001 AB      03/15/2010         490
         987001 AX      03/15/2010         370
    
      3 record(s) selected.

Posting Permissions

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