elcadro elcadro - 6 months ago 31
SQL Question

Select distinct rows with max date with repeated and null values (Oracle)

I've 3 tables. Let's say Root, Detail and Revision

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.

Root: idRoot, Code
Detail: idDetail, price, idRoot
Revision: idRevision, date, idDetail


So, i've started doing the join query:

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;


Having table results like this:

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


But what I really need is the next result:

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


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

Oracle: Taking the record with the max date

Fetch the row which has the Max value for a column

Oracle Select Max Date on Multiple records

Any kind of help would be really appreciated

Answer

You can use row_number():

select code, price, date
from (select code, price, date,
            row_number() over (partition by code order by date desc nulls last, idRevision desc) as seqnum
      from Root r inner join
           Detail d
           on d.idRoot = r.idRoot left join
           Revision r
           on d.idDetail = r.idDetail
     ) rdr
where seqnum = 1;