How to SQL query actual Pricelist grouped by several columns-CodePudding

I have following table in postgres (product and variant are foreign key ids to another tables but I used product/variant names to make it more readable):

id	productId	variantId	validFrom	currencyCode	price
1	iphone	X	2021-01-01	EUR	19
2	iphone	X	2021-02-01	EUR	29
3	iphone	X	2021-03-01	EUR	39
4	iphone	12	2021-01-01	EUR	49
5	macbook	air	2021-01-01	EUR	59
6	macbook	air	2021-02-01	EUR	69
7	macbook	pro	2021-02-01	EUR	79
8	macbook	pro	2021-01-01	USD	89

Now when I need pricelist valid for a date 2021-01-20 the result should be:

product	variant	validFrom	currencyCode	price
iphone	X	2021-01-01	EUR	19
iphone	12	2021-01-01	EUR	49
macbook	air	2021-01-01	EUR	59
macbook	pro	2021-01-01	USD	89

but when I need pricelist valid for a date 2021-02-08 the result should be:

product	variant	validFrom	currencyCode	price
iphone	X	2021-02-01	EUR	29
iphone	12	2021-01-01	EUR	49
macbook	air	2021-02-01	EUR	69
macbook	pro	2021-02-01	EUR	79
macbook	pro	2021-01-01	USD	89

Any suggestion how to write the query?

Many thanks

CodePudding user response：


WITH cte AS
  (SELECT productid AS Product,
          variantid AS variant,
          validfrom,
          currencycode,
          price,
          Row_number() OVER(PARTITION BY productid, variantid
                            ORDER BY To_date(validfrom, 'YYYY-MM-DD') DESC) AS RNUM
   FROM tbl
   WHERE To_date(validfrom, 'YYYY-MM-DD') <= '2021-02-08')
SELECT product,
       variant,
       validfrom,
       currencycode,
       price
FROM cte
WHERE rnum = 1;

Here tbl is the tableName

CodePudding user response：

here is one way :

select * from table t1
where id in ( 
  select id from table t2 
  where t1.productId = t2.productId 
  and t1.careintid = t2.varientid
  and t2.validfrom<= '<date param>'
  order by validfrom desc limit 1
)

or using window function:

select * from(
   select *, row_number() over (partition by productid, varientid order by case when validfrom <= '<dateparam>' then 1 else 0 end desc, validfrom desc) rn
) t where rn = 1