mySQL gives syntax error on subquery with valid syntax

Question

I'm trying to find the film that has been rented the most without using limit. I'm trying to use the following query:

SELECT f.title, f.film_id
FROM film f
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON r.inventory_id = i.inventory_id
GROUP BY f.film_id
HAVING COUNT(r.rental_id) = MAX(
    SELECT COUNT(r2.rental_id)
    FROM rental r2, inventory i2
    WHERE i2.inventory_id = r2.inventory_id
    GROUP BY i2.film_id);

but mySQL tells me that I have a syntax error somewhere in here SELECT COUNT(r2.rental_id) FROM rental r2, inventory however, when I run the subquery independently it returns the expected table. Am I doing something massively wrong?

relevant database schema:

film(film  id, title, description, release year, language id, original language id, rental duration, rental rate, length, replacement cost, rating, special features, last update)
inventory(inventory id, film id, store id, last update)
rental(rental id, rental date, inventory id, customer id, return date, staff id, last update)

I don't think you can have a query inside MAX()...

Hamza Kubba
– Hamza Kubba

2013-10-15 02:28:32 +00:00
Commented Oct 15, 2013 at 2:28 — Hamza Kubba
– Hamza Kubba, Commented Oct 15, 2013 at 2:28

Bohemian · Accepted Answer · 2013-10-15 05:17:09Z

1

You can't use MAX() over a result set, but you can use

someValue >= ALL (subquery)

to achieve what you're attempting, because ALL requires that the preceding operator be true for all values in the set.

Try this:

SELECT f.title, f.film_id
FROM film f
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON r.inventory_id = i.inventory_id
GROUP BY f.film_id
HAVING COUNT(r.rental_id) >= ALL (
    SELECT COUNT(r2.rental_id)
    FROM rental r2, inventory i2
    WHERE i2.inventory_id = r2.inventory_id
    GROUP BY i2.film_id);

edited Oct 15, 2013 at 5:17

answered Oct 15, 2013 at 4:19

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kag0 Over a year ago

How should I use max in this context? my subquery definitely returns more than one line, as there is more than one film_id in inventory and at least one rental corresponding to each inventory entry.

kag0 Over a year ago

FACEPALM, how did I not think of that, so obvious in hindsight. thanks.

Tom · Accepted Answer · 2013-10-15 02:58:14Z

0

I don't have a database to test in, but this should work:

Edited to LIMIT 1 instead of SELECT TOP 1 for MySQL)

SELECT f.title, f.film_id
FROM film f
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON r.inventory_id = i.inventory_id
GROUP BY f.film_id
HAVING COUNT(r.rental_id) = (SELECT COUNT(r2.rental_id)
                              FROM rental r2, inventory i2
                              WHERE i2.inventory_id = r2.inventory_id
                              GROUP BY i2.film_id
                              ORDER BY COUNT(r2.rental_id) desc
                              LIMIT 1) s

edited Oct 15, 2013 at 2:58

answered Oct 15, 2013 at 2:41

Tom

2,2407 gold badges34 silver badges49 bronze badges

3 Comments

kag0 Over a year ago

I don't think mySQL supports TOP

Tom Over a year ago

Looks like you're correct, serves me right... I've modified to the MySQL equivalent (LIMIT 1)

kag0 Over a year ago

and I cannot use limit (mentioned in the question).

Collectives™ on Stack Overflow

mySQL gives syntax error on subquery with valid syntax

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related