Get last fixed price per product from table-CodePudding

It seems to be an easy question by SQL, but I can't get it done in MySQL.

I have a table with prices for different products (ProdID) valid from certain dates and related from the quantity. For the current price list I need to get the price valid since the latest valid_from date in the table. The timestamp cannot be used as sometimes the prices inserted are for future valid_from dates, same with the ID, with is not representative to the actual prices.

ID, ProdID, qty, price, valid_from, timestamp
100 51  25      3.360   2021-02-15  2021-05-11 19:20:28
101 51  2000    3.150   2021-02-15  2021-05-11 19:20:29
102 51  6000    2.930   2021-02-15  2021-05-11 19:20:30
103 51  15000   2.870   2021-02-15  2021-05-11 19:20:31
131 51  1000    3.250   2021-02-15  2021-05-11 19:20:59
....
140 51  25      3.970   2021-10-06  2021-10-06 16:51:48
141 51  1000    3.790   2021-10-06  2021-10-06 16:51:50
142 51  2000    3.650   2021-10-06  2021-10-06 17:45:49
143 51  6000    3.500   2021-10-06  2021-10-06 16:51:54
144 51  15000   3.400   2021-10-06  2021-10-06 16:51:56

For example, these are the rows for ProdID 51.

I need to get the prices which are currently valid. In this case the ID 141 to 144 but this is only coincidentally. Also prices may be reduced so I can't go for the highest prices per prodID. The only criteria is the latest valid_from date which is <= date(). As mentioned there could be also some already inserted prices for the future (> date()).

I tried this, but it brings all above rows, valid from 2021-05-11 AND those valid from 2021-10-06:

SELECT `p`.`qty` AS `quantity`,
       `p`.`price` AS `price`,
       `p`.`ProdID` AS `ProdID`,
       row_number() OVER (PARTITION BY `p`.`ProdID`
                          ORDER BY `p`.`valid_from` DESC,`p`.`qty`) AS `rk`
FROM `tblprices` `p`

CodePudding user response：

You can use window functions. Use RANK to find greatest row(s) per group:

WITH cte AS (
  SELECT *, RANK() OVER (PARTITION BY ProdID ORDER BY valid_from DESC) AS rnk
  FROM t
  WHERE valid_from <= CURRENT_DATE
)
SELECT *
FROM cte
WHERE rnk = 1