jVr jVr - 1 year ago 52
MySQL Question

MySQL: Select rank from grouped result

I have a table which contains data from a radio station, and which songs it played. All the songs played are stored in a table named 'radio_data'. This table looks like this:

-----------------------------------------------
| id | artist_id | song_id | play_date |
| 1 230 420 2017-5-12 12:00 |
| 2 212 971 2017-5-12 12:01 |
| 3 230 420 2017-5-12 13:00 |
| 4 230 420 2017-5-12 15:00 |
| 5 212 971 2017-5-12 15:02 |
-----------------------------------------------


I have a page where some statistics for one specific song is displayed. On this page i would like to show the rank of the song, based on how much it has been played.

Let's say that if i am om the page for song_id 420, it would rank as place 1 out of 2 songs.
I have no idea where to start. I have this query to group the songs:

SELECT COUNT(`id`) AS `playcount`, `artist_id`, `song_id` FROM `radio_data` GROUP BY `song_id` ORDER BY `playcount` DESC


This gives me the following result:

-----------------------------------
| playcount | artist_id | song_id |
| 3 230 420 |
| 2 212 971 |
-----------------------------------


Thanks in advance for your help!

Answer Source

You can use user variables for displaying the rank:

set @rn := 0;

select @rn := @rn + 1 as rank,
    song_id,
    artist_id,
    count(*) as times_played
from your_table
group by song_id, artist_id
order by times_played desc;

If you want to get top, say 10, songs you can add a limit 10 at the end.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download