Get rows with most recent date for each different item
Skinning Q#1 independently and slightly differently than @ypercube
with cte as (select row_number() over (partition by product_id,
invoice_id
order by dt desc) as rn,
product_id,
invoice_id,
amount,dt
from product )
select product_id, invoice_id,amount,dt
from cte
where rn=1
order by product_id,invoice_id;
product_id | invoice_id | amount | dt
------------+------------+--------+------------
PROD1 | INV01 | 2 | 2018-01-05
PROD1 | INV03 | 1 | 2018-01-05
PROD2 | INV02 | 3 | 2018-01-08
PROD2 | INV04 | 4 | 2018-01-08
(4 rows)
For Q#2, you are on the right track, but the SQL will have a cross join(gasp!)
I think a function with a loop/cursor would be more optimized (i'll try that in my next free block of time)
--the cte will give us the real values
with cte as (select product_id,
sum(amount) as amount,
dt
from product
group by product_id,dt)
select p.product_id,
(select cte.amount --choose the amount
from cte
where cte.product_id = p.product_id
and cte.dt <= d.gdt -- for same day or earlier
order by cte.dt desc
limit 1) as finamt,
d.gdt
from (select generate_series( (select min(dt)
from product), --where clause if some products
--don't have an amount
(select max(dt)
from product),
'1 day'
)::date as gdt) d
cross join --assuming each listed product has an amount on the min date
(select distinct product_id
from product) p
left join --since we need to fill the gaps
cte on ( d.gdt = cte.dt
and p.product_id = cte.product_id)
order by d.gdt, p.product_id
;
I understand that you want all the rows with the latest date for every product (ties included, i.e. all rows with the last date). This can be done with rank()
function:
select id, product_id, invoice_id, amount, date
from
( select id, product_id, invoice_id, amount, date,
rank() over (partition by product_id
order by date desc) as rnk
from
-- your joins
) as t
where rnk = 1 ;