Sort posts by highest vote count?

Question

SELECT 
links.id, 
links.url, 
links.user_id, 
links.title, 
links.description, 
links.nsfw, 
links.posted, 
links.category_id,

votes.vote
votes.vote_date

FROM links 

INNER JOIN votes
ON votes.link_id = links.id

ORDER BY (I want to sort by the count of votes on the links) DESC

If I want to sort my links/posts by votes, how can I then do it with this SQL? The link with the highest vote count shows first.

The thing is unclear. You want a list of links sorted by *average* vote? By most voted? By highest-given-vote? ..? — redShadow, Dec 18 '11 at 17:59

Sergio Tulentsev · Accepted Answer · 2011-12-18T18:00:01.677

1

You want grouping. Try this:

SELECT 
  links.id, 
  links.url, 
  links.user_id, 
  links.title, 
  links.description, 
  links.nsfw, 
  links.posted, 
  links.category_id
FROM 
  links 
  INNER JOIN votes ON votes.link_id = links.id
GROUP BY links.id
ORDER BY COUNT(votes.id) DESC

I didn't test it (because I don't have your dataset), but it should be more or less correct.

edited Dec 18 '11 at 18:00

answered Dec 18 '11 at 17:53

Sergio Tulentsev

226,338
43
373
367

I believe he does *not* want to group. – Álvaro González Dec 18 '11 at 17:54
I believe he _does_ want highest voted posts. How to provide that without grouping on links? – Sergio Tulentsev Dec 18 '11 at 17:59
Yeah, I admit it's pretty difficult. I suppose that's why we had to ask ;-) – Álvaro González Dec 18 '11 at 18:02

score 0 · Answer 2 · answered Dec 18 '11 at 17:54

0

Group by link id and select thee vot count

SELECT
    count(*) as votecount,
    links.id, 
    links.url, 
    links.user_id, 
    links.title, 
    links.description, 
    links.nsfw, 
    links.posted, 
    links.category_id,
    votes.vote
    votes.vote_date
FROM links 
INNER JOIN votes
    ON votes.link_id = links.id
GROUP BY links.id

2.) Wrap this in a superquery for sorting

SELECT * FROM (
    <as above>
) AS baseview
ORDER BY votecount DESC

answered Dec 18 '11 at 17:54

Eugen Rieck

64,175
10
70
92

The difference between this solution and the one by @SergeiTulentsev is, that the inner query will go to the query cache. Assuming you might need it for display later on, this is a good thing. – Eugen Rieck Dec 18 '11 at 17:56
query cache gets invalidated on every table mutation. So I never rely on this :-) – Sergio Tulentsev Dec 18 '11 at 17:57

score 0 · Answer 3 · answered Dec 18 '11 at 18:06

You can use a subquery:

SELECT links.id, links.url, votes.vote, votes.vote_date
FROM links
INNER JOIN votes ON votes.link_id = links.id
INNER JOIN (
    SELECT links.id AS link_id, COUNT(votes.id) AS num_votes
    FROM links
    INNER JOIN votes ON votes.link_id = links.id
    GROUP BY links.id
) AS votes_per_link ON links.id=votes_per_link.link_id
ORDER BY votes_per_link.num_votes DESC

redShadow · Answer 4 · 2011-12-18T18:13:55.510

Since I didn't understand which of these behaviors you are asking for, I post solutions to different cases here:

Get links, order by highest vote:

SELECT votes.link_id, links.url, MAX(votes.vote) AS maxvote
FROM votes INNER JOIN links ON links.id = votes.link_id
GROUP BY votes.link_id ORDER BY `maxvote` DESC;

Get links, order by average vote:

SELECT votes.link_id, links.url, AVG(votes.vote) AS avgvote
FROM votes INNER JOIN links ON links.id = votes.link_id
GROUP BY votes.link_id ORDER BY `avgvote` DESC;

Get links, order by votes count:

SELECT votes.link_id, links.url, COUNT(votes.vote) AS cntvotes
FROM votes INNER JOIN links ON links.id = votes.link_id
GROUP BY votes.link_id ORDER BY `cntvotes` DESC;

Get votes, by highest vote:

SELECT links.id, links.url, votes.vote
FROM links INNER JOIN votes ON links.id = votes.link_id
ORDER BY votes.vote DESC;

(I omitted some fields to shorten queries, of course feel free to add them back again).

Sort posts by highest vote count?

4 Answers4