1

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

Community
  • 1
  • 1
elcadro
  • 1,482
  • 3
  • 21
  • 45

1 Answers1

3

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 2
    it seems OP wants order max revision for latest date. Shouldn't that be (partition by code order by date desc nulls last, idrevision desc) ? – micklesh May 27 '16 at 12:35
  • Thanks a lot, this is what I was looking for. As micklesh said, it should be ordered by date first, but still... ;) – elcadro May 27 '16 at 14:21