I trying to make a query to obtain the last date time when a records repeat.
select * from OCCONTROLMERCADERIA om
join OCCONTROLMERCADERIALINEAS oml on om.occontrolid=oml.occontrolid
where om.OCControlNroId=5519337
This return this:
The query that i want should return the register are in the blue box.
I have this query that found (without the join):
with
p as (select * from OCCONTROLMERCADERIA om where om.OCControlNroId=5519337),
p_rnk as (
select *, row_number() over (partition by OCControlNroId order by OCControlFecha desc) as rn
from p
)
select * from p_rnk where rn = 1 order by OCControlNroId;
This return this:
That its ok, if see the box blue in the first image, the last date corresponds to the record id 13756.
But I need that the query return the 3 records that corresponds to id 13756, and not only one, so i trying to apply the join.
with
p as (select * from OCCONTROLMERCADERIA om
join OCCONTROLMERCADERIALINEAS oml on om.occontrolid=oml.occontrolid
where om.OCControlNroId=5519337),
p_rnk as (
select *, row_number() over (partition by OCControlNroId order by OCControlFecha desc) as rn
from p
)
select * from p_rnk where rn = 1 order by OCControlNroId;
But this return:
The column 'OCControlId' was specified multiple times for 'p'.
I´m trying this (select om.*):
with
p as (select om.* from OCCONTROLMERCADERIA om
join OCCONTROLMERCADERIALINEAS oml on om.occontrolid=oml.occontrolid
where om.OCControlNroId=5519337),
p_rnk as (
select *, row_number() over (partition by OCControlNroId order by OCControlFecha desc) as rn
from p
)
select * from p_rnk where rn = 1 order by OCControlNroId;
And the error disappears but only return one records, no the three. Which could the problem ?
Thanks for your help!