sql - sort query by other relationship count

Question

This is how my tables are.

So a song can have more votes. I want to get all songs, sorted by how many votes it has. How can I do that?

Example: the song table would be

1,"Master Of Puppets"
2,"Don't Cry"
3,"Baby"
4,"Song name"
5,"I want to break free"

and the votes:

1,5
2,5
3,5
4,2
5,2
6,1

I want to query the song table to get the most voted songs, which should be in this order

5,"I want to break free"
2,"Don't cry"
1,"Master Of Puppets"
3,"Baby"
4,"Song name"

Is there a way to do this with only one query ? I know that I can do a query for all songs, and then for each song query the votes table where the songId is that current song id, but can I do this with only one query?

Answer 1

Use Count() Aggregate to count the number of votes each song has got and use it in Order by.

Left Outer join is used to return rows from song table even though a song never got a vote

Try this

SELECT s.id, 
       s.title 
FROM   song s 
       LEFT OUTER JOIN votes v 
                    ON s.id = v.songid 
GROUP  BY s.id, 
          s.title 
ORDER  BY Count(v.songid) DESC 

Answer 2

SELECT s.id, s.title FROM
(
  SELECT s.id, s.title, count(v.id) votes
  FROM 
   song s
  JOIN vote v on s.id = v.songId
   GROUP BY s.id, s.title
   ORDER BY votes desc 
);

can be also an option.