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

DB2begin · #1 (**permalink**) 03-17-10, 21:50

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.

Marcus_A · #2 (**permalink**) 03-17-10, 21:59

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)

Vaibhav Vyas · #3 (**permalink**) 03-18-10, 01:43

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

tonkuma · #4 (**permalink**) 03-19-10, 08:29

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech