Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL return first row of a joined table

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.

like image 376
Al_ Avatar asked Mar 04 '12 21:03

Al_


2 Answers

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.

like image 114
ypercubeᵀᴹ Avatar answered Sep 21 '22 03:09

ypercubeᵀᴹ


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
like image 43
Naltharial Avatar answered Sep 21 '22 03:09

Naltharial