MySQL return first row of a joined table

Question

I have two tables (country & ducks) where the country table has every country in the world and the ducks table has a list of ducks with a country_id field to link to the main country.

I'm trying to get a list of only countries with at least one duck in it and with that a single matching record from the ducks table for the highest rated duck within that country. So far I have:

SELECT *
FROM country c 
INNER JOIN ducks d ON c.id = d.country_id
ORDER BY c.country ASC, d.rating DESC

This returns a list of every duck rather than just one per country.

I'd be grateful if anyone can point me in the right direction here. I'd rather do it in SQL than have a separate query for each country to pull out the top rated duck.

Answer 1

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.

Answer 2

You could try just adding a selecting join, for

SELECT c.*, d.*
FROM country c 
INNER JOIN ducks d ON c.id = d.country_id
LEFT JOIN ducks d2 ON d.country_id = d2.country_id AND d2.rating > d.rating
WHERE d2.id IS NULL