Oracle: Scenario (Get the max date and its corresponding flag from table)

Posted: November 4, 2015 in Oracle

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s