SQL Case depending on previous status of record
<p>I have a table containing status of a records. Something like this:</p>

<pre><code>ID  STATUS  TIMESTAMP
1   I   01-01-2016
1   A   01-03-2016
1   P   01-04-2016
2   I   01-01-2016
2   P   01-02-2016
3   P   01-01-2016
</code></pre>

<p>I want to make a case where I take the newest version of each row, and for all P that has at some point been an I, they should be cased as a 'G' instead of P.</p>

<p>When I try to do something like </p>

<pre><code>Select case when ID in (select ID from TABLE where ID = 'I') else ID END as     status) 
From TABLE
where ID in (select max(ID) from TABLE)
</code></pre>

<p>I get an error that this isn't possible using IN when casing.</p>

<p>So my question is, how do I do it then?</p>

<p>Want to end up with:</p>

<pre><code>ID  STATUS  TIMESTAMP
1   G   01-04-2016
2   G   01-02-2016
3   P   01-01-2016
</code></pre>

<p>DBMS is IBM DB2</p>