I have a product table that has code. some products have both code 50 and code 51 but that doesn't matter to me. I'd like to just get the product with the most code-independent data. So if the date of that product with code 51 is greater than code 50 I want to get it.
I'm having difficulties, when I put the max(data) again using the query as from I have the same values that is, it's as if it didn't come to anything.
Select
x.num_obra,
x.dsc_status,
o.dth_operacao,
o.cod_operacao,
o.mot_operacao
from
(select
a.num_obra,
T.dth_operacao,
a.mot_operacao,
a.cod_operacao
from
obra_log a,
(select
num_obra,
cod_operacao,
max(dth_operacao) as dth_operacao
from
obra_log
where cod_operacao in ('51', '50')
group by num_obra,cod_operacao) t
where a.num_obra = t.num_obra
and a.cod_operacao = t.cod_operacao
and a.dth_operacao = t.dth_operacao
) o,
(select
b.num_obra,
c.dsc_status
from
obra b,
status c
where b.cod_regional in ('10', '20', '30', '40', '50', '60', '65', '70')
and b.cod_status = c.cod_status
and b.cod_status in ('20', '21', '59', '60')) x
where x.num_obra = o.num_obra(+)