Rawhi Rawhi - 1 year ago 75
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
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 Source

Try this:

  (SELECT a.id AS aid,
          a.name AS artist,
          s.id AS sid,
          s.name AS most_viewed,
   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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download