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