Archive for the ‘Oracle’ Category

Below is the data we have in table CODE_XREF

ID DATE1 FLAG1 DATE2 FLAG2
101 10/5/2015 AB 10/4/2015 A
101 10/6/2015 AC 10/5/2015 AA
101 10/7/2015 AD 10/6/2015 AC
102 10/5/2015 DD 10/6/2015 D
102 10/6/2015 DD 10/7/2015 DC
102 10/7/2015 DE 10/8/2015 DD
104 10/7/2015 XX 10/8/2015 X
105 10/7/2015 HI 10/8/2015 C
105 10/8/2015 GI 10/9/2015 CC

Required Output :- 

ID DATE1 FLAG1 DATE2 FLAG2
101 10/7/2015 AD 10/6/2015 AC
102 10/7/2015 DE 10/8/2015 DD
104 10/7/2015 XX 10/8/2015 X
105 10/8/2015 GI 10/9/2015 CC

This can be implemented in ‘n’ number of ways. But today will see how to implement this using analytical functions.

SELECT DISTINCT A.*
FROM (Select ID,
MAX(DATE1) OVER (PARTITION BY ID ORDER BY ID) AS MAX_DATE1,
FIRST_VALUE(FLAG1) OVER (PARTITION BY ID ORDER BY DATE1 DESC) AS MAX_DATE_OF_FLAG1,
MAX(DATE2) OVER (PARTITION BY ID ORDER BY ID) AS MAX_DATE2,
FIRST_VALUE(FLAG2) OVER (PARTITION BY ID ORDER BY DATE2 DESC) AS MAX_DATE_OF_FLAG2
FROM CODE_XREF ) A

Advertisements