SQL: Querying 2 tables and sort results by values in another table

Is it possible to select all the comments for a single record and order them by the overall ratio of positive:negative votes from another table?

I think I may need to use a subselect but I really am not sure how or where.

2 tables:

table 1 [Comments]

ID  |  RecordID  |  Comment  
------------------------------
1   | 100001     | blah blah
2   | 100202     | another co
3   | 100054     | lorem ips

table 2 [Ratings]

ID  | CommentID  |  Vote  
-------------------------
1   | 1          | 1       
2   | 1          | 0
3   | 1          | 1
4   | 3          | 0
5   | 3          | 0
6   | 3          | 0

Please note: 'Vote' : 0 = negative vote; 1 = positive vote Also, I am using Microsoft SQL Server

Thanks for any help :)

Answers


To sort them from positive to negative rating you can do:

SELECT c.id,
  c.recordID,
  c.comment
FROM comments c
INNER JOIN (
  SELECT CommentID,
  SUM(CASE WHEN Vote = 0 THEN -1 ELSE 1 END) AS RATING FROM ratings
  GROUP BY commentID
  ) r ON r.commentID = c.id
ORDER BY r.RATING DESC;

sqlfiddle demo

Note that i am using the CASE to give votes with 0 a value of -1, otherwise, 10 negatives and one positive would give a rating of 1.

This query is not taking into account the comments without scores. If you want to have the comments without scores to have 0 rating, you could do:

SELECT c.id,
  c.recordID,
  c.comment
FROM comments c
LEFT JOIN (
  SELECT CommentID,
  SUM(CASE WHEN Vote = 0 THEN -1 ELSE 1 END) AS RATING FROM ratings
  GROUP BY commentID
  ) r ON r.commentID = c.id
ORDER BY COALESCE(r.RATING,0) DESC;

EDIT:

To get the ratio try this:

SELECT c.id,
  c.recordID,
  c.comment,
  r.rating
FROM comments c
LEFT JOIN (
  SELECT CommentID,
    SUM(CASE 
        WHEN Vote = 1
          THEN 1
        ELSE 0
        END) / (nullif(COUNT(*) * 1.0, 0)) AS RATING
  FROM ratings
  GROUP BY commentID
  ) r ON r.commentID = c.id
ORDER BY COALESCE(r.RATING, 0) DESC;

sqlfiddle demo


Need Your Help

Username maximum length in Devise 3.2

ruby-on-rails ruby-on-rails-3 devise ruby-on-rails-4

When I try to save a new password, I get an error that a user's username is too long (I'm using the username mechanism for Devise with Rails 3.2.x here). How do I increase the length a username can...

Best JavaScript compressor

javascript compression

What is the the best JavaScript compressor available? I'm looking for a tool that: