how to filter selected query with another condition in postgresql

Question

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

Unrelated to your problem, but: Postgres 9.1 is [no longer supported](https://www.postgresql.org/support/versioning/) you should plan an upgrade as soon as possible. — , Nov 21 '21 at 16:13
i need the rows whose closing stock > 0, so i added that condition — pappu_kutty, Nov 21 '21 at 16:23
But in your expected results the row with id=1 has closing_stock=0 — forpas, Nov 21 '21 at 16:24
Check what is written in your question under "but i am expecting below results". The 1st row with id=1 has closing_stock=0. — forpas, Nov 21 '21 at 16:47

score 1 · Accepted Answer · answered Nov 21 '21 at 17:04

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.

simple and straight forward solution.. – pappu_kutty Nov 21 '21 at 17:11 — pappu_kutty, Nov 21 '21 at 17:11

how to filter selected query with another condition in postgresql

1 Answers1