CODEWITHSUNDEEP
mysql
Mariano Grandioso
Mariano Grandioso

Reputation: 1205

MySQL - ordering images by votes

I haven't used pure mySQL for a while now and looks like I forgot it. Sad story. Now I have 2 tables - images and votes. Images have a unique id, among others. The votes table only has 2 fields - image id and user id.

I'm trying to get the list of images ordered by number of votes and I'm kinda stuck here. The closest I got is this:

SELECT i.uname AS author, i.title, i.time, i.description, i.id, i.extension, v.uid
FROM images as i
LEFT JOIN
votes as v ON i.id = v.iid

Which returns all images with the voter ID. If an image has multiple votes, than it's returned more than once.

Can someone please help me with this query ?

Upvotes: 0

Views: 48

Answers (2)

user359040
user359040

Reputation:

Try:

SELECT i.uname AS author, i.title, i.time, i.description, i.id, i.extension, count(*) votes
FROM images as i
LEFT JOIN votes as v ON i.id = v.iid
group by i.id
order by 7

(from lowest to highest - add desc after order by 7 to change the sort order to be from highest to lowest.)

Upvotes: 0

sgeddes
sgeddes

Reputation: 62841

You need to use COUNT and GROUP BY:

SELECT i.uname AS author, i.title, i.time, i.description, i.id, i.extension, COUNT(v.uid)
FROM images as i
    LEFT JOIN votes as v ON i.id = v.iid
GROUP BY i.uname, i.title, i.time, i.description, i.id, i.extension
ORDER BY Count(v.uid) DESC

As suggested, you don't have to GROUP BY all your output fields in MySQL -- just use your unique identifier, in this case, i.id.

Upvotes: 5

Related Questions