SQL selection of last period
<p>I have a table like this:</p>

<p><div class="snippet" data-lang="js" data-hide="false">
<div class="snippet-code">
<pre class="snippet-code-html lang-html prettyprint-override"><code>&lt;table border="1" cellspacing="0" cellpadding="5"&gt;
&lt;tr&gt;&lt;td&gt;ID&lt;/td&gt;&lt;td&gt;status&lt;/td&gt;&lt;td&gt;start_date&lt;/td&gt;&lt;td&gt;end_date&lt;/td&gt;&lt;/tr&gt;
&lt;tr&gt;&lt;td&gt;1&lt;/td&gt;&lt;td&gt;A&lt;/td&gt;&lt;td&gt;2015-01-01&lt;/td&gt;&lt;td&gt;2015-12-31&lt;/td&gt;&lt;/tr&gt;
&lt;tr&gt;&lt;td&gt;1&lt;/td&gt;&lt;td&gt;B&lt;/td&gt;&lt;td&gt;2016-01-01&lt;/td&gt;&lt;td&gt;NULL&lt;/td&gt;&lt;/tr&gt;
&lt;tr&gt;&lt;td&gt;2&lt;/td&gt;&lt;td&gt;C&lt;/td&gt;&lt;td&gt;NULL&lt;/td&gt;&lt;td&gt;2016-12-31&lt;/td&gt;&lt;/tr&gt;
&lt;tr&gt;&lt;td&gt;3&lt;/td&gt;&lt;td&gt;D&lt;/td&gt;&lt;td&gt;NULL&lt;/td&gt;&lt;td&gt;NULL&lt;/td&gt;&lt;/tr&gt;
&lt;/table&gt;</code></pre>
</div>
</div>
</p>

<p>I must do a trigger in MySQL to update a main table with the last status of a subject.</p>

<p>To do this I must select for every <code>ID</code> the last <code>status</code> based on the period between <code>start_date</code> and <code>end_date</code> that can be NULL if didn't known.</p>

<p>I write this SQL statement test:</p>

<pre><code>SELECT *
FROM My_Table AS T
WHERE T.start_date Is Null AND
T.end_date In(SELECT MAX(end_date) FROM My_Table WHERE ID = T.ID)
OR
T.start_date In(SELECT MAX(start_date) FROM My_Table WHERE ID = T.ID) AND 
T.end_date Is Null
OR
T.start_date In(SELECT MAX(start_date) FROM My_Table WHERE ID = T.ID) AND
T.end_date In(SELECT MAX(end_date) FROM My_Table WHERE ID = T.ID
);
</code></pre>

<p>Is there a shorten method to do this?</p>

<p>In practice I want a WHERE clause that catch each of these cases (a = start_date, b=end_date):</p>

<p><code>a = MAX AND b = MAX OR
a = NULL AND b = MAX OR
a = MAX AND b = NULL OR
a = NULL AND b = NULL</code></p>