title: Select MAX from 2 tables
body: <p>I have the following select and I would like to return only the most recent TranDate when the Tracct occurs more than once.</p>
  
  <pre><code>SELECT tracct, trancd, trnsta, date(digits(trdat7)) as TranDate, type 
  FROM DATALIBRARY.LNHIST LNHIST 
  JOIN DATALIBRARY.LNMAST LNMAST 
    on LNHIST.TRACCT = LNMAST.ACCTNO 
  WHERE YEAR(date(digits(trdat7))) &gt;= YEAR(current_date) - 2 and trnsta = '1' and trancd = 891 and type not like 'I%'
  </code></pre>
  
  <p>Current result:</p>
  
  <pre><code>|Tracct | Trancd | Trnsta | TranDate   | Type|
  ----------------------------------------------
   425660 | 891    | 1      | 2013-05-10 | C5  |
   102649 | 891    | 1      | 2013-05-10 | C5  |
   102741 | 891    | 1      | 2015-08-08 | RO  |
   102741 | 891    | 1      | 2015-09-10 | RO  |
   102741 | 891    | 1      | 2014-05-10 | RO  |
   115298 | 891    | 1      | 2013-03-31 | CV  |
   102313 | 891    | 1      | 2015-04-10 | CL  |
   102313 | 891    | 1      | 2015-05-10 | CL  |
  </code></pre>