Rawhi Rawhi - 1 month ago 10
C# Question

using group by to fetch results

I have two tables in my database :


song => id, name, song_ref, views
artist => id, name


I want to fetch all the artists from the song table with the most viewed song for each one.

I've tried to use
MAX(views)
to get the max views value, but didn't succeed to get the other fields for this relevant song.

SELECT a.name, MAX(s.views) FROM song s inner join artist a
ON s.artist_ref = a.idArtist GROUP BY a.name


any ideas ? thank you

Answer

Try this:

SELECT *
FROM
  (SELECT a.id AS aid,
          a.name AS artist,
          s.id AS sid,
          s.name AS most_viewed,
          s.views
   FROM artist a
   INNER JOIN song s ON s.artist_ref=a.id
   ORDER BY a.id,
            s.views DESC) c
GROUP BY c.aid;