Problem joining on the highest value in mysql table

Answer 1

Begin and end dates on your history table would make this possible.(leaving the most recent end date null and stamping end dates on the previous record as you insert a new one)

Otherwise you will have to use a sub-query.

Answer 2

That same query is parsable in MySQL.

Why are you using a Left JOIN instead of an INNER join or a RIGHT join?

Also if you want to go about this in a different way, you have the MAX function at your disposal.

Answer 3

Here's how I'd do it:

SELECT p.*, r.*
FROM products AS p
  JOIN revisions AS r USING (product_id)
  LEFT OUTER JOIN revisions AS r2 
    ON (r.product_id = r2.product_id AND r.modified < r2.modified)
WHERE r2.revision_id IS NULL;

In other words: find the revision for which no other revision exists with the same product_id and a greater modified value.