Select count votes for each object and top voted objects

I have two t开发者_StackOverflowables :

VOTE with fields pk:id, fk:uid, date and SUBMISSION with fields pk:id, ....

Tables have 1to1 relation on uid<-id fields. How I can now query for :

• list of objects together with their score
• list of 10 top rated objects ordered by score ?

---

    SELECT s.*,
           COUNT(*) AS cnt
      FROM SUBMISSION s
INNER JOIN VOTE v ON s.id = v.uid
  GROUP BY s.id
  ORDER BY cnt DESC
     LIMIT 10

Without ORDER BY and GROUP BY clauses you'll just retrieve all the submissions with votes count.

But I highly recommend you to create votes_count field in the SUBMISSION table and maintain it with trigger/code to store the precalculated count of votes there.