MySQL correlated subquery at FROM

Question

I'm working with the Sakila sample database, and trying to get the most viewed film per country. So far I've managed to get the most viewed film of a certain country given its id with the following query:

SELECT 
    F.title, CO.country, count(F.film_id) as times
FROM 
    customer C 
INNER JOIN 
    address A ON C.address_id = A.address_id
INNER JOIN 
    city CI ON A.city_id = CI.city_id
INNER JOIN 
    country CO ON CI.country_id = CO.country_id
INNER JOIN 
    rental R ON C.customer_id = R.customer_id
INNER JOIN 
    inventory I ON R.inventory_id = I.inventory_id
INNER JOIN 
    film F ON I.film_id = F.film_id
WHERE 
    CO.country_id = 1
GROUP BY 
    F.film_id
ORDER BY 
    times DESC
LIMIT 1;

I supose that I'll have to use this query or something similar in the FORM of another query, but I've tried it all I could think and am completely unable to figure out how to do so.

Thanks in advance!

Answer 1

I admit, this is a hell of a query. But well, as long as it works.

Explanation:

• Subquery: almost the same as you already has. Without the WHERE and LIMIT. Resulting in a list of movie-count per country
• Result of that, grouped per country
• GROUP_CONCAT(title ORDER BY times DESC SEPARATOR '|||'), will give ALL titles in that 'row', with the most-viewed title first. The separator doesn't matter, as long as you are sure it will never occurs in a title.
• SUBSTRING_INDEX('...', '|||', 1) results in the first part of the string until it finds |||, in this case the first (and thus most-viewed) title

Full query:

SELECT
    country_name,
    SUBSTRING_INDEX(
        GROUP_CONCAT(title ORDER BY times DESC SEPARATOR '|||'), 
        '|||', 1
    ) as title,
    MAX(times)
FROM (
    SELECT 
        F.title AS title, 
        CO.country_id AS country_id,
        CO.country AS country_name, 
        count(F.film_id) as times
    FROM customer C INNER JOIN address A ON C.address_id = A.address_id
    INNER JOIN city CI ON A.city_id = CI.city_id
    INNER JOIN country CO ON CI.country_id = CO.country_id
    INNER JOIN rental R ON C.customer_id = R.customer_id
    INNER JOIN inventory I ON R.inventory_id = I.inventory_id
    INNER JOIN film F ON I.film_id = F.film_id
    GROUP BY F.film_id, CO.country_id
) AS count_per_movie_per_country
GROUP BY country_id

Proof of concept (as long as the subquery is correct): SQLFiddle