how to write this sql

Question

I'm using mysql

there are two tables

vote

app_id user_id
2      1
3      1
2      2

app

id title content
1  xx    xxxx   
2  yy    yyyy
3  zz    zzzz

I want to sort app table by the number of user's vote . in this example , the result should be

id title content
2  yy    yyyy
3  zz    zzzz
1  xx    xxxx

any suggestion?

Answer 1

select app.id, app.title, count(*) as votes
from app
left join vote on app.id = vote.app_id
group by app.id, app.title
order by votes desc

When you do not want to have the number of votes in the output, you can do this as well (in sql server it is possible, so i believe it should be possible in mySql as well, but I haven't tested it since I have no MySQL here).

select app.id, app.title
from app
left join vote on app.id = vote.app_id
group by app.id, app.title
order by count(*) desc

Answer 2

Group on the data from the app table to get a count of votes, and use a left join on the vote table to also get the applications without votes:

select app.id, app.title, app.content
from app
left join vote on vote.app_id = app.id
group by app.id, app.title, app.content
order by count(vote.app_id) desc