Selecting 1 record based on last unique value that is on another column
<p>I need help with the example below, this example is the result of joining 4 tables with a left outer join because I need to check that all column even knowing that could get null values, but what I really want is to get 1 record for each C6 based on the last o higher value on c6.</p>

<pre><code>C1  C2  C3  C4  C5  C6
4   A   MM  90  GT  798
4   A   MM  90  GT  625
4   A   MM  90  GT  354
4   A   MM  90  GT  547
5   K   EE  60  SV  213
5   K   EE  60  SV  235
5   K   EE  60  SV  236
9   O   WW  40  PE  456
9   O   WW  40  PE  487
9   O   WW  40  PE  982
</code></pre>

<p>The query that I am using is like this:</p>

<p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
<div class="snippet-code">
<pre class="snippet-code-html lang-html prettyprint-override"><code>SELECT distinct   C1, C2, C3, C4,C5,C6
   --,row_number() OVER(partition by c6 ORDER BY c1 asc)       
  FROM table1 n
  left outer join  table 2 j on j.cad = n.Cad and j.P = n.P and j.H = 'Name'
  left outer join  table 3 k on k.id = n.id
  left outer join  table 4 m on m.u = n.u and m.s = n.s and m.Cad = n.Cad  and n.P = m.P
where NOT EXISTS   (SELECT 1 from table 5 v WHERE N.U=v.U and N.S=v.S 
                       and N.Cad=v.Cad and N.P=v.P) and n.Cad is not null
        and C6 is not null</code></pre>
</div>
</div>
</p>