CODEWITHSUNDEEP
phpmysqlsql
user3766332
user3766332

Reputation: 329

MySQL query for ranking by column

I am running a query in php which gives me a table in this format:

+--------------------------------------------------------------------------+
|   Name               cc          Platform         Playcount      Date    |
+--------------------------------------------------------------------------+
| The Godfather      IN           Web              20000            20140701 |
| The Godfather      IN           Mob              210            20140701 |
| The Godfather      US           Web              221            20140701  |
| The Godfather      US           Mob              1200            20140701 |
| Pulp Fiction      IN           Web              1300            20140701  |
| Pulp Fiction      IN           Mob              20            20140701  |
| Pulp Fiction      US           Web              222            20140701   |
| Pulp Fiction      US           Mob              100            20140701  |
| Fight Club      IN           Web              2002            20140701    |
| Fight Club      IN           Mob              2100            20140701    |
| Fight Club      US           Web              2           20140701     |
| Fight Club      US           Mob              1000            20140701    |
+--------------------------------------------------------------------------+

The problem is, that I need to rank them by total playcount. That is , The Godfather clearly has the highest number of total playcount in this table. After ranking, I need to select the top 20 total playcount movies, and the corresponding 4 rows have to be entered into a separate table.

How can I do this ranking to get the total and then insert all four rows corresponding to that into a new table?

So, in this example table, if I want items having top 2 rank, I'll insert this in my table

+--------------------------------------------------------------------------+
|   Name               cc          Platform         Playcount      Date    |
+--------------------------------------------------------------------------+
| The Godfather      IN           Web              20000            20140701 |
| The Godfather      IN           Mob              210            20140701 |
| The Godfather      US           Web              221            20140701  |
| The Godfather      US           Mob              1200            20140701 |
| Fight Club      IN           Web              2002            20140701    |
| Fight Club      IN           Mob              2100            20140701    |
| Fight Club      US           Web              2           20140701     |
| Fight Club      US           Mob              1000            20140701    |
+--------------------------------------------------------------------------+

Upvotes: 0

Views: 82

Answers (2)

Jacky Cheng
Jacky Cheng

Reputation: 1556

Here's a horriblely formated (and probably buggy LoL) query that should do what you need. Welcome anyone to provide feedback to improve it.

INSERT INTO top20 (name, cc, platform, playcount, date) 
SELECT 
    Name, 
    cc,
    Platform,
    Playcount,
    date
FROM
(SELECT 
    m.Name, 
    m.cc,
    m.Platform,
    m.Playcount,
    m.date,
    m2.TotalPlayCount 
FROM Movies m
LEFT JOIN (
    SELECT 
        Name, 
        SUM(Playcount) as TotalPlayCount 
    FROM Movies 
    GROUP BY Name 
    ORDER BY TotalPlayCount DESC 
    LIMIT 20) as m2
USING (Name)
ORDER BY TotalPlayCount DESC, Name, cc, Platform) as temp
-- if the final inserted data is not in the order you want, add more ORDER clause here

Upvotes: 1

Cas Wolters
Cas Wolters

Reputation: 371

How about a group by in combination with an order by?

SELECT 
    Name, 
    SUM(Playcount) as TotalPlayCount 
FROM Movies 
GROUP BY Name 
ORDER BY TotalPlayCount DESC 
LIMIT 0, 20

This then can be used to add in into a new table

INSERT INTO Statistics (Name, Playcount) 
SELECT 
    Name, 
    SUM(Playcount) as TotalPlayCount 
FROM Movies 
GROUP BY Name 
ORDER BY TotalPlayCount DESC

Upvotes: 0

Related Questions