greatest n per group - Getting most recent data if data for the day is not available with MySQL

Question

I have a large SQL query (MySQL 5.7.32) that gets all sorts of data AND is including pricing data for a particular day, if there is price data available for this day. Done by a simple LEFT JOIN:

SELECT *
FROM merchants m CROSS JOIN products p
LEFT JOIN prices mps
    ON m.id = mps.id AND p.article_id = mps.article_id AND mps.DATE = $date

In some cases there is no price for this particular day. In that case I would like to get the last recent price available.

Is this possible with LEFT JOIN or do I have to add even more complexity to my statement?

Answer 1

Yes it is possible

SELECT m.*, t.*
FROM merchants m
JOIN (
    SELECT MAX(`date`) as max_date, id
    FROM prices
    WHERE `date` <= ?
    GROUP BY id
) t ON t.id = m.id

Edit : 2 steps queries

Assuming your shop is displaying 20 products per pages. You can run a 1st query like this :

SELECT m.*
FROM merchants m
WHERE some_criterias
LIMIT 20 OFFSET 0

and then you pass the result of this 1st query to the 2nd one :

SELECT m.*, t.*
FROM merchants m
JOIN (
    SELECT MAX(`date`) as max_date, p.merchant_id
    FROM prices p
    AND merchant_id IN (?, ?, ?...)
    WHERE `date` <= ?
    GROUP BY id
) t ON t.merchant_id = m.id