2

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:

Result Query 1

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:

Result Query 2

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!

0 Answers0