CODEWITHSUNDEEP
sqloracle-database
elcadro
elcadro

Reputation: 1492

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

Upvotes: 1

Views: 1798

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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;

Upvotes: 3

Related Questions