0

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 > 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 > 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) > my_max_date - interval '30 day' 
  and item_id = 3232;

i can't figure it out.

0

Editing your last query to the solution:

select * 
from items_prices 
where date_item >= ( 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 >= 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 >= 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
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.