How would I query for top three songs played by users?

Question

How would I query top 3 songs played from user? userid and song_name is char(255)

Here is the description of the table:

 +-----------+-----------+------+-----+-------------------+-------+
 | Field     | Type      | Null | Key | Default           | Extra |
 +-----------+-----------+------+-----+-------------------+-------+
 | userid    | char(255) | YES  |     | NULL              |       |
 | song_name | char(255) | YES  |     | NULL              |       |
 | timestamp | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
 +-----------+-----------+------+-----+-------------------+-------+

Here is sample of what a table would look like:

    +--------+-----------+---------------------+
    | userid | song_name | timestamp           |
    +--------+-----------+---------------------+
    | 123    | A         | 2017-01-04 01:35:41 |
    | 123    | B         | 2017-01-04 01:37:57 |
    | 123    | B         | 2017-01-04 01:38:32 |
    | 123    | B         | 2017-01-04 01:38:42 |
    | 123    | C         | 2017-01-04 01:38:46 |
    | 123    | D         | 2017-01-04 01:38:50 |
    | 123    | E         | 2017-01-04 01:38:54 |
    | 123    | E         | 2017-01-04 01:38:59 |
    | 123    | A         | 2017-01-04 01:39:03 |
    | 123    | E         | 2017-01-04 01:39:20 |

I changed the song_names to letters for you to better understand. A top song really would be how many time a song_name correspond to the userid.

Current code:

 SELECT userid, GROUP_CONCAT(DISTINCT song_name 
 ORDER BY song_name) 
 top_songs
 FROM sampleTable3
 GROUP BY userid;

How would I alter it that it would print out the top 3 songs order by most played?

My question is asking how many time a specific value corresponds to userid.

If it is a top-n-per group question, that also has an answer on SO: http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results — Shadow, Jan 04 '17 at 11:55
@Shadow That code counts how many values corresponds to colums. — , Jan 04 '17 at 11:57
In my case, it would count how many songs correspond to userid. — , Jan 04 '17 at 11:57
Then see the 2nd link I gave you. Your description was not clear. — Shadow, Jan 04 '17 at 11:59

score 1 · Answer 1 · answered Jan 04 '17 at 12:12

1

try this

select count(*) as cnt,song_name,userid from songs group by song_name order by cnt DESC limit 3;

demo on sqlfiddle

answered Jan 04 '17 at 12:12

denny

2,084
2
15
19

does not work. also, the table name is sampletable3 – Jan 04 '17 at 12:16
@Huy why not work? – denny Jan 04 '17 at 12:50
doesn't matter, u got ur point. keep up the work stud. – Jan 04 '17 at 13:09

Gurwinder Singh · Accepted Answer · 2017-01-04T12:25:25.763

Copied and fixed from Gordon's answer. (Sorry Gordon)

SELECT userid, GROUP_CONCAT(song_name ORDER BY cnt DESC SEPARATOR '|')
FROM (SELECT us.*,
             (@rn := if(@u = userid, @rn + 1,
                        if(@u := userid, 1, 1)
                       )
             ) as rn
      FROM (SELECT userid, song_name, COUNT(*) as cnt
            FROM sampleTable3
            GROUP BY userid, song_name
            ORDER BY userid, COUNT(*) DESC
           ) us CROSS JOIN
           (SELECT @u := -1, @rn := 0) t1
     ) u
WHERE rn <= 3
GROUP BY userid;

If you want to find top 3 songs for only one given user, then use:

select userid, songname
from songs
where userid = 123
group by userid, songname
order by count(*) desc
limit 3;

SQLFiddle

Gordon Linoff · Answer 3 · 2017-01-04T12:06:28.537

Actually, you can pursue this method, but you need to aggregate first and adjust the group_concat() logic:

SELECT userid,
       SUBSTRING_INDEX(GROUP_CONCAT(song_name ORDER BY cnt DESC SEPARATOR '|'), '|', 3)
 top_songs
FROM (SELECT userid, song_name, COUNT(*) as cnt
      FROM sampleTable3
      GROUP BY userid, song_name
     ) us
GROUP BY userid;

However, you are likely to get a string overflow error, because the intermediate result is limited. You can change the limit, which is a system parameter.

An alternative method uses variables:

SELECT userid, GROUP_CONCAT(song_name ORDER BY cnt DESC SEPARATOR '|')
FROM (SELECT us.*,
             (@rn := if(@u = userid, @rn + 1,
                        if(@u := userid, 1, 1)
                       )
             ) as rn
      FROM (SELECT userid, song_name, COUNT(*) as cnt
            FROM sampleTable3
            GROUP BY userid, song_name
            ORDER BY userid, COUNT(*) DESC
           ) us CROSS JOIN
           (SELECT @u := -1, @rn := 0)
      GROUP BY userid
     ) u
WHERE rn <= 3;

@HuyVo . . . This particular question is a good reason why MySQL should not be used to store data for data analysis. This would be much easier in a database that supports ANSI-compliant window functions. — Gordon Linoff, Jan 04 '17 at 12:23

How would I query for top three songs played by users?

3 Answers3