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
