MySQL Rank Product downloads

Question

I have two tables, the first is the Videos table which has the details of every videos.

CREATE TABLE IF NOT EXISTS `Videos` (

 `VideoID` varchar(23) NOT NULL AUTO_INCREMENT,

`VideoTitle` varchar(50) NOT NULL,

);

The second records the download of each video

CREATE TABLE IF NOT EXISTS `Downloads` (
 `downloadID` int(11) NOT NULL AUTO_INCREMENT,
`userID` int(11) NOT NULL,
`VideoID` varchar(23) NOT NULL,

);

Every record in the Downloads table equates to a single download.

How do I compile a list of the top ten most downloaded items

order by a count on downloads. as for the top ten and ranking, that question has been asked here before... search for it. — Barett, Apr 07 '15 at 18:42
possible duplicate of [MySQL select statement - How to calculate the current ranking](http://stackoverflow.com/questions/27394516/mysql-select-statement-how-to-calculate-the-current-ranking) — Barett, Apr 07 '15 at 18:43

Hanky Panky · Accepted Answer · 2015-04-07T17:47:49.553

How are you using a VARCHAR field for AUTO_INCREMENT? Have i missed some MySQL Updates?

Nope i haven't, MySQL still doesn't allow that.

Use a large enough integer data type for the AUTO_INCREMENT column to hold the maximum sequence value you will need.

Reference

Once the schema is fixed, you can do

SELECT VideoTitle,count(downloadID) as d FROM Downloads
INNER JOIN Videos ON Videos.VideoId=Downloads.VideoID 
GROUP BY Downloads.VideoID
ORDER BY d DESC
LIMIT 10

P.S: If you had provided some sample data I would be happy to put up a fiddle, feeling lazy without it :)

MySQL Rank Product downloads

1 Answers1