Query for latest price for each seller per product

Question

I asked a very similar question yesterday

Query for latest price per seller per product

It was marked as duplicate but the proposed answer is unfortunately not helping me because the case is not the same or I don't see the solution.

Please see my refined question because my original post was also not 100% correct.

I have a database table which looks as following:

id    product_id     product_name     price     date            seller
1     646            Product 1        1         2020-05-20      Seller-A
2     1554           Product 2        1.50      2020-05-23      Seller-B
3     646            Product 1        2         2020-05-22      Seller-C
4     646            Product 1        2.5       2020-05-23      Seller-A

As a result I would like to get the latest info based on the date for every product_id but also for every seller.

 2     1554           Product 2        1.50      2020-05-23      Seller-B 
 3     646            Product 1        2         2020-05-22      Seller-C
 4     646            Product 1        2.5       2020-05-23      Seller-A

-> Result 1 should not be returned because Seller A has a price which is more up to date with id 4

I have the following query which works fine for 1 product id:

SELECT * FROM (SELECT * FROM `table` WHERE 1 ORDER BY `date` DESC) t2 WHERE product_id = 646 GROUP BY `seller`

How should I build the query to get the results for multiple id's. At the moment it will only return the result for every seller available but no matter how many product ids are available

Answer 1

One option is to filter with a subquery:

select t.*
from mytable t
where t.date = (select max(t1.date) from mytable t1.seller = t.seller)

For performance, consider an index on (seller, date).