sql server join joining 3 tables to give highest date out multiple values
<p>I have 3 tables (a, b, c) they are linked by acc_no (a.acc_no, b.acc_no, c.acc_no)</p>

<p>and I'm trying to join them in a way to obtain the highest row value:</p>

<pre><code>a.acc_no    a.seq_no    b.open_dt    b.close_dt    c.chg_dt
--------    --------    ---------    ----------    ----------
100001      500         2016-07-01   2016-07-16    2016-09-02
100001      500         2016-07-01   2016-07-16    2016-09-05
100001      510         2016-07-17   2016-09-30    2016-09-02
100001      510         2016-07-17   2016-09-30    2016-09-05
100002      502         2016-09-02   2016-09-27    2016-09-03
100003      503         2016-09-03   2016-09-26    2016-09-25
</code></pre>

<p>how do i make it look like this:   </p>

<pre><code>a.acc_no    a.seq_no    b.open_dt    b.close_dt    c.chg_dt
--------    --------    ---------    ----------    ----------
100001      510         2016-07-17   2016-09-30    2016-09-05
100002      502         2016-09-02   2016-09-27    2016-09-03
100003      503         2016-09-03   2016-09-26    2016-09-25
</code></pre>

<p>basically disregard the lowest a.seq_no where the acc_no is the same and also display the highest chang_dt possible out of the two i'm let with.</p>

<p>I've tried everything but I either don't get it or I'm doing something wrong when I join the tables.</p>

<p>To clarify the tables:</p>

<pre><code>TABLE a    TABLE b    TABLE c
-------    -------    -------
acc_no     acc_no     acc_no
seq_no     open_dt    chang_dt
           close_dt
</code></pre>

<p>all 3 tables are linked by acc_no</p>