MySQL - Find most recent, best price for item from multiple vendors

Question

I'm currently having trouble finding a solution for my problem and you guys are my last hope. Since two days I'm trying to solve this puzzle:

table item:

----------------------------
| id   | item   | customer |
----------------------------
| 1    | banana | custA    |
----------------------------
| 2    | apple  | custA    |
----------------------------
| 3    | orange | custB    |
----------------------------
| 4    | apple  | custB    |
----------------------------

table vendor_prices:

-------------------------------------------------------
| id  |   item    |   price   |   vendor |  timestamp |
-------------------------------------------------------
| 1   |  banana   |   0.23    | VendorA  |  564645564 |
-------------------------------------------------------
| 2   |  orange   |   0.21    | VendorA  |  564645564 |
-------------------------------------------------------
| 3   |  apple    |   0.19    | VendorB  |  564645564 |
-------------------------------------------------------
| 4   |  banana   |   0.22    | VendorB  |  564645565 |
-------------------------------------------------------
| 5   |  banana   |   0.21    | VendorB  |  564645567 |
-------------------------------------------------------

There are a few things to note:

1. Not every vendor has every item
2. Some vendors might update their prices more often than other resulting in a larger gap between them in timestamp

For example, I want to know which vendor sells bananas currently for the best price?

I think I would first need to get the most recent price from each vendor for every item, and then sort those by price, right? But how to do that in a MySQL compatible way?

I think the correct way for the first part is:

SELECT MAX(timestamp), vendor, item, MIN(price) FROM vendor_prices WHERE item="banana" GROUP BY vendor;

But how to connect this to all the other criterias?

EDIT: I had to change the first table a bit, since I forgot an important part in the question, sorry :(

Expected output: The most recent, best price of all vendors for all items for a specific customer in the first table (custA or custB)

Answer 1

You could use nested groupwise maximum logic for your criteria

select a.*
from vendor_prices a
join (
  select item, min(price) price, max(timestamp) timestamp
  from (
    select d.*
    from vendor_prices d
    join (
      select item, vendor, max(timestamp) timestamp
      from vendor_prices
      group by item, vendor
    ) e using(item, vendor,timestamp )
  ) c
  group by item
) b using (item,price, timestamp)
where a.item = 'banana'

Demo

If you are using Mysql 8+ you can take benefit using window functions

Edit *get all items for a customer (best price and vendor for each item) *

select  i.*,a.*
from vendor_prices a
join (
  select  item, min(price) price, max(timestamp) timestamp
  from (
    select  d.*
    from vendor_prices d
    join (
      select  item, vendor, max(timestamp) timestamp
      from vendor_prices
      group by item, vendor
    ) e using(item, vendor,timestamp )
  ) c
  group by item
) b using (item,price,timestamp )
join item i using(item)
where i.customer = 'custA'
order by i.item

Demo

Using window function and common table expression which are available in Mysql 8 you could use following

with latest_price as(
    select *, 
    dense_rank() over (partition by item order by timestamp desc, price asc ) rnk
    from vendor_prices
    order by  item, rnk
)

select i.id itemid, i.customer,a.* 
from latest_price a
join item i using(item)
where i.customer = 'custA'
and a.rnk = 1

Demo