Select distinct rows with max date with repeated and null values (Oracle)
<p>I've 3 tables. Let's say Root, Detail and Revision</p>

<p>I need to select the distinct codes from Root with the highest revision date, having count that the revision lines may not exist and/or have repeteated values in the date column.</p>

<pre><code>Root: idRoot, Code
Detail: idDetail, price, idRoot
Revision: idRevision, date, idDetail
</code></pre>

<p>So, i've started doing the join query:</p>

<pre><code>select code, price, date from Root r 
inner join Detail d on d.idRoot = r.idRoot
left join Revision r on d.idDetail = r.idDetail;
</code></pre>

<p>Having table results like this:</p>

<pre><code>CODE|PRICE|DATE     idRevision
---- ----- -----    -----------
C1    100  2/1/2016      1
C1    120  2/1/2016      3
C1    150  null          2
C1    200  1/1/2016      4
C2    300  null          null
C3    400  3/1/2016      6
</code></pre>

<p>But what I really need is the next result:</p>

<pre><code>CODE|PRICE|DATE     idRevision
---- ----- -----    -----------
C1    120  2/1/2016   3 
C2    300  null      null 
C3    400  3/1/2016   6
</code></pre>

<p>I've seen several answers for similar cases, but never with null and repeated values:</p>

<p><a href="https://stackoverflow.com/questions/8898020/oracle-taking-the-record-with-the-max-date">Oracle: Taking the record with the max date</a></p>

<p><a href="https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column">Fetch the row which has the Max value for a column</a></p>

<p><a href="https://stackoverflow.com/questions/10761665/oracle-select-max-date-on-multiple-records">Oracle Select Max Date on Multiple records</a></p>

<p>Any kind of help would be really appreciated</p>