CODEWITHSUNDEEP
group-bydb2
user2416710
user2416710

Reputation: 53

Need help in Group by in DB2

This is Parent_Child table.

PARENT  CHILD   EFF_DATE
22716   2528    3/8/2011
22716   5696    3/8/2011
22716   3698    3/8/2011
22716   5698    3/18/2010
37091   4569    10/22/2013
37091   6931    9/17/2014

Query result should look like this:

PARENT  CHILD   EFF_DATE
22716   2528    3/8/2011
22716   5696    3/8/2011
22716   3698    3/8/2011
37091   6931    9/17/2014

Query tried:

SELECT DISTINCT P.PARENT,P.CHILD,C.MAX_DATE
FROM parent_child P
INNER JOIN
(SELECT CHILD,MAX(EFF_DT) AS MAX_DATE 
FROM parent_child
GROUP BY CHILD) C
ON P.CHILD=C.CHILD AND P.EFF_DT=C.MAX_DATE
ORDER BY P.PARENT

But I end up getting both the values of parent 37091.

Appreciate any help on this.

Upvotes: 0

Views: 32

Answers (1)

amdixon
amdixon

Reputation: 3833

Changes required

  • change inner query to group on parent instead of child
  • change join condition to match on parent

query

SELECT DISTINCT P.PARENT,P.CHILD,C.MAX_DATE
  FROM parent_child P
  INNER JOIN
    (SELECT PARENT,MAX(EFF_DT) AS MAX_DATE 
      FROM parent_child
      GROUP BY PARENT) C
  ON  P.PARENT=C.PARENT
  AND P.EFF_DT=C.MAX_DATE
  ORDER BY P.PARENT
;

output

PARENT    CHILD   MAX_DATE
22716     2528    March, 08 2011 00:00:00
22716     5696    March, 08 2011 00:00:00
22716     3698    March, 08 2011 00:00:00
37091     6931    September, 17 2014 00:00:00

sqlfiddle

Upvotes: 1

Related Questions