Can you use ORDER BY (column) LIMIT with a secondary column?

I want to find out the Top 10 rented movies in the sakila db. It must be sorted DESC after total_rentals and secondary after film title. My solution looks like this and works so far without the film title:

SELECT COUNT(rental_date) AS total_rentals, f.title
FROM rental r 
LEFT JOIN inventory i USING (inventory_id) 
LEFT JOIN film f USING (film_id)
Group by f.title
ORDER BY total_rentals DESC LIMIT 10, f.title 
;

However, I can’t sort it after film.title due to syntax error(Error 1064).

Can someone help me out?

Answer

Is this what you want?

ORDER BY total_rentals DESC, f.title  LIMIT 10 

This guarantees just 10 rows in the resultset, sorted by descending total_rentals, then by film title.

Another way to read your question: give me the 10 best rental scores, and the associated movies – which may yield more than 10 rows if there are ties. In that case, I would recommend window functions (available in MySLA 8.0)

SELECT *
FROM (
    SELECT COUNT(*) AS total_rentals, f.title,
        DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) rn
    FROM rental r 
    INNER JOIN inventory i USING (inventory_id) 
    INNER JOIN film f USING (film_id)
    GROUP BY film_id, f.title
) t
WHERE rn <= 10
ORDER BY total_rentals DESC, title 

Notes:

• COUNT(*) is more efficient than COUNT(<something>) and is equivalent here

• I wouldn’t expect “orphan” rows in rental; if so, INNER JOINs are more appropriate than LEFT JOINs

• It is probably a good idea to add film_id to the GROUP BY clause, in case there are title homonyms