Choosing Single Row from multiple rows - Teradata
<p>I have a requirement below to choose latest status.</p>

<p>Table 1:</p>

<p><a href="https://i.stack.imgur.com/0BpCU.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0BpCU.png" alt="enter image description here"></a></p>

<p>Table2:</p>

<p><a href="https://i.stack.imgur.com/A0VeM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/A0VeM.png" alt="enter image description here"></a></p>

<p>Results Expected:</p>

<p><a href="https://i.stack.imgur.com/9L1Q9.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9L1Q9.png" alt="enter image description here"></a></p>

<p>Below is the logic we need.</p>

<pre><code>    SELECT
        Table1.ID,
       ,CASE WHEN (Table1.hub=Table2.hub) THEN Table2.Status ELSE NULL END AS Original_Status
       ,CASE WHEN (Table1.hub&lt;&gt;Table2.hub AND Table2.Status like 'Found%' ) THEN Table2.hub ELSE NULL END AS Derived_Hub
       ,CASE WHEN (Table1.hub&lt;&gt;Table2.hub AND Table2.Status like 'Found%' ) THEN Table2.Status ELSE NULL END AS Derived_Status
from
    Table1 
Join Table2
    ON (Table1.ID=Table2.ID)
</code></pre>

<p>With this code, I am getting 3 rows. If I put max in the above case statements, I am getting Hub3 instead of Hub2.</p>

<p>Could you please let me know how I can merge everything into single row. Please note that I want to show latest found Status from Table 2 even if there are 2 hubs with found status.</p>

<p>Let me know if you need any further information.</p>