get top unique 10 artists by their song view count-CodePudding

I have 3 tables. Artists, SongArtists, Songs.

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

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

Thanks

Edit

The answer is -

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;

CodePudding user response：

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

CodePudding user response：

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).