Count top 10 inputs in a table

Question

I have a function on my site where people can vote on photos. Everytime someone click the vote-button my script adds a row in a table (mysql).

Each row looks like this: (Table name = likes)

id --------- userId --------- photoName --------- date

1 ----------- 21 -------------- 34234 ----------- 20100101

How do i find the top 10 photos that people has been voting on? I tried "SELECT TOP 10 photoName FROM likes" but that didnt work...

Answer 1

SELECT
  COUNT(*) as points,
  photoName
FROM likes
GROUP BY photoName
ORDER BY points DESC
LIMIT 10;

Answer 2

You'll have to use some count with a group by ; something like this should help :

select photoName, count(*) as nbLikes
from likes
group by photoName
order by count(*) desc
limit 0, 10

i.e. you group by photoName, and you count how many times each photo has been liked -- and order by that number in desc order, to get the most liked ; and, finally, you use limit to only fetch the 10 first lines.