MySQL Aggregate statistic with subquery in the table list

Viewed 17

I am working on an example query in sakila where I want to show the customer with the max count of rentals. Lots of ways to do this, both simpler and preferable, but I wanted to use a subquery in the table list. I got here and celebrated, until I quickly realized the rookie mistake: I get the max count, but just the first row from the left table.

SELECT customer.customer_id, first_name, last_name, max(Num_rentals) 
FROM customer
JOIN   
    (SELECT rental.customer_id, count(*) AS num_rentals
    FROM rental
    GROUP BY rental.customer_id) as Sub
ON customer.customer_id = sub.customer_id
;

I have tried a number of iterations to try to get at answer I want, and I would like something a bit more elegant than sorting the subquery and limiting the result there to a single row. BTW the correct answer is Elenor Hunt with 46. Any help is appreciated.

0 Answers
Related