MySQL return first row of a joined table
SELECT c.*, d.*FROM country c INNER JOIN ducks d ON d.id = --- guessing the ducks Primary Key here ( SELECT dd.id --- and here FROM ducks dd WHERE c.id = dd.country_id ORDER BY dd.rating DESC LIMIT 1 )An index on (country_id, rating, id) for MyISAM table or (country_id, rating) for InnoDB table, would help.
This query will show only one duck per country, even with more than one having the same rating. If you want ducks with tied rating to appear, use @imm's GROUP BY answer.
You could try just adding a selecting join, for
SELECT c.*, d.*FROM country c INNER JOIN ducks d ON c.id = d.country_idLEFT JOIN ducks d2 ON d.country_id = d2.country_id AND d2.rating > d.ratingWHERE d2.id IS NULL
You might try:
SELECT c.*, d.*FROM country cINNER JOIN ( SELECT d.country_id, d.id, MAX(d.rating) AS rating FROM ducks d GROUP BY d.country_id) q ON (q.country_id = c.id)INNER JOIN ducks d ON (d.country_id, d.rating) = (q.country_id, q.rating)