Postgres : Select items for an interval from the max date in store I have a database with items looking like this :

 id , item_id , date_item , price
 0  , 3232    , 2019-04-22, 10
 0  , 3232    , 2019-04-23, 4
 0  , 3232    , 2019-04-24, 1
 0  , 3232    , 2019-04-25, 3
 0  , 3232    , 2019-04-26, 4
 0  , 3232    , 2019-04-29, 5
 0  , 3232    , 2019-04-30, 8
 0  , 3232    , 2019-05-01, 9
 0  , 3232    , 2019-05-02, 54
 0  , 3232    , 2019-05-03, 43
 0  , 3232    , 2019-05-06, 7
 0  , 3232    , 2019-05-07, 56


and more ( many item_id  and date_item )...

I'm trying to select the last 30 days of items in database but can't figure out the exact query.

currently i'm doing this : 

select * 
from items_prices 
where date_item &gt; current_timestamp - interval '30 day' 
  and item_id = 3232;


Which output the above result because we are the 05-20 and the last item in store is the 05-07.

What im trying to do is to always select the lastest date item in DB and go 30 days from there.

I've been trying to do it with max() but i can't really figure it out and my query doesn't work:

select * 
from items_prices 
where date_item &gt; max(date_item) - interval '30 day' 
  and item_id = 3232;


or something like this :

select * 
from items_prices 
where my_max_date=(SELECT id, MAX(date_item) AS "Max Date" 
                   FROM items_prices 
                   where item_id = 3232) &gt; my_max_date - interval '30 day' 
  and item_id = 3232;


i can't figure it out.
Editing your last query to the solution:

select * 
from items_prices 
where date_item &gt;= ( SELECT MAX(date_item) - interval '30 day' 
                     FROM items_prices 
                     where item_id = 3232 )
  and item_id = 3232;


The subquery can be moved to CTE:

WITH cte AS ( SELECT MAX(date_item) - interval '30 day' lastday
              FROM items_prices 
              where item_id = 3232 )
select items_prices.* 
from items_prices, cte
where items_prices.date_item &gt;= cte.lastday
  and items_prices.item_id = 3232;


or (to select last records for more than one item_id, maybe all)

WITH cte AS ( SELECT item_id, MAX(date_item) - interval '30 day' lastday
              FROM items_prices )
select items_prices.* 
from items_prices, cte
where items_prices.date_item &gt;= cte.lastday
  and items_prices.item_id = cte.item_id
--  and items_prices.item_id = (one item_id value, for example, 3232)
--  and items_prices.item_id IN (item_id list, for example, 3232, 3233)
--  none for all item_id
;