Russel Russel - 13 days ago 6
SQL Question

Oracle Getting latest value from the other table

I have two tables, table1 contains old values and table2 contains latest values, I want to show latest value in table1 but I do not have anything which tells me this is the latest value in table2.

for example

Table1

CID-----PID-----RID
CT1-----C-------R1
CT2-----C-------R2
CT3-----C-------R3
CT4-----C-------R4


Table2

CID-----PID----RID
CT1-----A-------R1
CT1-----C-------R11
CT2-----C-------R2
CT3-----A-------R3
CT4-----A-------R4


The condition is I have to give priority to value C in case both values (A and C) exist also it's RID changes so need to get that also in output table, for the same CID and for unique value I will simple replace it in table1 from table2, so output will be like this

Table3

CID-----PID----RID
CT1-----C-------R11
CT2-----C-------R2
CT3-----A-------R3
CT4-----A-------R4

Answer

I may be missing something, but isn't this simply:

select cid, max(pid)
from table2
group by cid;

If you want whole records, use a ranking with ROW_NUMBER instead:

select cid, pid, rid
from
(
  select cid, pid, rid, row_number() over (partition by cid order by pid desc) as rn
  from table2
)
where rn = 1;

You can also use case expressions for ranking, e.g.:

(partition by cid order by case pid when 'C' then 1 when 'A' then 2 else 3 end) as rn

UPDATE: Now that you've finally explained what you are after ...

You want more or less the second query I gave you above. Only that you want data from both tables, which you can get with UNION ALL. You can easily give each row a rank on the way:

  • table2 PIM C => rank #1
  • table2 PIM A => rank #2
  • table1 rank #3

Then again take the row with the best rank:

select cid, pid, rid
from
(
  select cid, pid, rid, row_number() over (partition by cid order by rnk) as rn
  from
  (
    select cid, pid, rid, case when pid = 'C' then 1 else 2 end as rnk from table2
    union
    select cid, pid, rid, 3 as rnk from table1
  )
)
where rn = 1;