i have table as below
id | product_id | product_type_id | closing_stock | created_dttm
-------------------------------------------------------------------------
1 2 1 0 21-Nov-21
2 2 2 9 21-Nov-21
3 2 3 11 21-Nov-21
4 2 1 7 20-Nov-21
i need to get last or recent records with unique product_id, and product_type_id order by created_dttm desc.
so i have below query, but it is not fetching last or recent entered data due to closing_stock param > 0.
select distinct on(product_id, product_type_id) *
from daily_stock
where product_id = 2
and product_type_id in (1, 2, 3)
and closing_stock > 0
order by product_id, product_type_id , created_dttm desc
id | product_id | product_type_id | closing_stock | created_dttm
-------------------------------------------------------------------------
1 2 2 9 21-Nov-21
2 2 3 11 21-Nov-21
3 2 1 7 20-Nov-21
but i am expecting below results
id | product_id | product_type_id | closing_stock | created_dttm
------------------------------------------------------------------------------------
2 2 2 9 21-Nov-21
3 2 3 11 21-Nov-21
The WHERE clause is applied before DISTINCT ON, filtering out all the rows with closing_stock = 0.
So, if any row with closing_stock = 0 is the latest for a combination of product_id and product_type_id this combination will not be excluded from the results.
Remove the condition closing_stock > 0 from your query and use it after you get the results:
select *
from (
select distinct on(product_id, product_type_id) *
from daily_stock
where product_id = 2 and product_type_id in (1, 2, 3)
order by product_id, product_type_id, created_dttm desc
) t
where closing_stock > 0;
Or, with row_number() window function:
select *
from (
select *, row_number() over (partition by product_id, product_type_id order by created_dttm desc) rn
from daily_stock
where product_id = 2
) t
where rn = 1 and closing_stock > 0;
See the demo.