Get last fixed price per product from table

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`

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0