get top unique 10 artists by their song view count

user_619540 0
Viewed 105

I have 3 tables. Artists, SongArtists, Songs.

Song can have multiple artists. Therefore, I have joint table called SongArtists and table structure is -

Songs ID | Name | URL | ViewCount | CreatedAt | UpdatedAt

Artists ID | Name | URL | CreatedAt | UpdatedAt

SongArtists ID | SongID | ArtistID

Song table has view_count column to store how many times the song is viewed.

I want to get top 10 artists based on their song view count.

I tried below query but same artist is shown twice. I cannot use group by because I need to songs.view_count to sort.

select artists.* 
from artists 
inner join song_artists 
  on song_artists.artist_id = artists.id 
inner join songs on songs.id = song_artists.song_id 
order by songs.view_count desc 
limit 10;

Please show me how I can achieve.

Example Data

Songs

ID | Name | URL | ViewCount | CreatedAt | UpdatedAt
1 | Song A | song-a | 154 | 2021-12-11 15:34:21 | 2021-12-11 15:34:21
2 | Song B | song-b | 54 | 2021-12-13 12:23:12 | 2021-12-13 12:23:12
3 | Song C | song-c | 123 | 2021-12-13 13:12:56 | 2021-12-13 13:12:56
4 | Song D | song-d | 15 | 2021-12-13 14:01:15 | 2021-12-13 14:01:15
5 | Song E | song-e | 26 | 2021-12-14 12:12:03 | 2021-12-14 12:12:03
6 | Song F | song-f | 165 | 2021-12-14 13:54:23 | 2021-12-14 13:54:23
7 | Song G | song-g | 121 | 2021-12-14 13:54:23 | 2021-12-14 13:54:23
8 | Song H | song-h | 135 | 2021-12-14 13:54:23 | 2021-12-14 13:54:23
9 | Song I | song-i | 25 | 2021-12-14 13:54:23 | 2021-12-14 13:54:23
10 | Song J | song-j | 15 | 2021-12-14 13:54:23 | 2021-12-14 13:54:23
11 | Song K | song-k | 26 | 2021-12-14 13:54:23 | 2021-12-14 13:54:23
12 | Song L | song-l | 5 | 2021-12-14 13:54:23 | 2021-12-14 13:54:23

Artists

ID | Name | URL | CreatedAt | UpdatedAt
1 | Artist A | artist-a | 2021-12-11 15:34:21 | 2021-12-11 15:34:21
2 | Artist B | artist-b | 2021-12-13 12:23:12 | 2021-12-13 12:23:12
3 | Artist C | artist-c | 2021-12-13 13:12:56 | 2021-12-13 13:12:56
4 | Artist D | artist-d | 2021-12-13 14:01:15 | 2021-12-13 14:01:15
5 | Artist E | artist-e | 2021-12-14 12:12:03 | 2021-12-14 12:12:03
6 | Artist F | artist-f | 2021-12-14 13:54:23 | 2021-12-14 13:54:23
7 | Artist G | artist-g | 2021-12-11 15:34:21 | 2021-12-11 15:34:21
8 | Artist H | artist-h | 2021-12-13 12:23:12 | 2021-12-13 12:23:12
9 | Artist I | artist-i | 2021-12-13 13:12:56 | 2021-12-13 13:12:56
10 | Artist J | artist-j | 2021-12-13 14:01:15 | 2021-12-13 14:01:15
11 | Artist K | artist-k | 2021-12-14 12:12:03 | 2021-12-14 12:12:03
12 | Artist L | artist-l | 2021-12-14 13:54:23 | 2021-12-14 13:54:23

SongAritsts

ID | SongID | ArtistID
1 | 1 | 3
2 | 2 | 2
3 | 3 | 1
4 | 4 | 10
5 | 5 | 11
6 | 6 | 12
7 | 7 | 9
8 | 8 | 8
9 | 9 | 7
10 | 10 | 6
11 | 11 | 5
12 | 12 | 4
13 | 12 | 1

Results Expected

ID | Name | URL | CreatedAt | UpdatedAt
12 | Artist L | artist-l | 2021-12-14 13:54:23 | 2021-12-14 13:54:23
3 | Artist C | artist-c | 2021-12-13 13:12:56 | 2021-12-13 13:12:56
8 | Artist H | artist-h | 2021-12-13 12:23:12 | 2021-12-13 12:23:12
1 | Artist A | artist-a | 2021-12-11 15:34:21 | 2021-12-11 15:34:21
9 | Artist I | artist-i | 2021-12-13 13:12:56 | 2021-12-13 13:12:56
2 | Artist B | artist-b | 2021-12-13 12:23:12 | 2021-12-13 12:23:12
11 | Artist K | artist-k | 2021-12-14 12:12:03 | 2021-12-14 12:12:03
5 | Artist E | artist-e | 2021-12-14 12:12:03 | 2021-12-14 12:12:03
7 | Artist G | artist-g | 2021-12-11 15:34:21 | 2021-12-11 15:34:21
10 | Artist J | artist-j | 2021-12-13 14:01:15 | 2021-12-13 14:01:15
mysql
2 Answers

You can still sort by the artist's MAX(view_count) if you use group by:

select artists.* 
from artists 
inner join song_artists 
  on song_artists.artist_id = artists.id 
inner join songs on songs.id = song_artists.song_id 
group by artists.<primary key>
order by MAX(songs.view_count) desc 
limit 10;

That will reduce the results to one row per artist, and sort by the view_count of their most-viewed song.

I used <primary key> in the example, but you would use id or whatever is the primary key of your table. In recent versions of MySQL, this is enough to cue the query parser that the other columns of artists are functionally dependent on the grouping column(s).

Bill_Karwin_208600

You could use:

select artists.*,t1.view_count 
from artists   
inner join (select artists.name, songs.view_count
            from artists 
            inner join song_artists  on song_artists.artist_id = artists.id 
            inner join songs on songs.id = song_artists.song_id 
            order by songs.view_count desc limit 10
           ) as t1 on t1.name=artists.name
order by view_count desc    ;

Result:

id    name          url              createdAt        updatedAt     view_count
12    Artist L    artist-l    2021-12-14 13:54:23 2021-12-14 13:54:23 165
3 Artist C    artist-c    2021-12-13 13:12:56 2021-12-13 13:12:56 154
8 Artist H    artist-h    2021-12-13 12:23:12 2021-12-13 12:23:12 135
1 Artist A    artist-a    2021-12-11 15:34:21 2021-12-11 15:34:21 123
9 Artist I    artist-i    2021-12-13 13:12:56 2021-12-13 13:12:56 121
2 Artist B    artist-b    2021-12-13 12:23:12 2021-12-13 12:23:12 54
5 Artist E    artist-e    2021-12-14 12:12:03 2021-12-14 12:12:03 26
11    Artist K    artist-k    2021-12-14 12:12:03 2021-12-14 12:12:03 26
7 Artist G    artist-g    2021-12-11 15:34:21 2021-12-11 15:34:21 25
6 Artist F    artist-f    2021-12-14 13:54:23 2021-12-14 13:54:23 15

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1772083455984557edf0b6aa7d03b5a9

**

Edit: @Lwin Htoo Ko below answer gave the desired result

**

select artists.* 
from artists 
inner join ( 
             select artists.id, 
                    songs.view_count 
             from artists 
             inner join song_artists on song_artists.artist_id = artists.id 
             inner join songs on songs.id = song_artists.song_id 
             group by artists.id, songs.view_count 
             order by songs.view_count desc 
             ) as top_artists on top_artists.id = artists.id 
group by artists.id limit 10;
user_164619520
Related