MySQL, Need to select rows that has the most frequent values in another table

I'm kind of new to SQL and I can't find the solution to my problem. I have two tables. In table A, I'm storing a lot of comments, each with a unique ID.

In table B, I'm storing every vote (like=1 and dislike=0) for every comment with a datetime. There will be an entry for every vote, so there will be tons of rows for each comme开发者_开发知识库nt in table A.

I need to retrieve all the comments and sort them such that the weekly most liked comments are at the top, but I'm not sure how.

Here's what I have so far, but not sure how to continue:

SELECT * FROM comment INNER JOIN logs ON comment.c_id=logs.c_id WHERE logs.daterate >= DATE_SUB(CURDATE(), INTERVAL 8 DAY) AND logs.rated=1 

To clarify, I need to get all entries from logs with rated = 1 in the past week and sort them by the most frequent c_id in descending order, and get distinct c_id for each row... if that makes sense

Please ask questions if I didn't make it clear enough, thanks!!

    SELECT *
      FROM comment
INNER JOIN (SELECT comment.c_id,
                   COUNT(*) AS cnt
              FROM comment
        INNER JOIN logs ON comment.c_id=logs.c_id
             WHERE logs.daterate >= DATE_SUB(CURDATE(), INTERVAL 8 DAY)
               AND logs.rated=1
          GROUP BY comment.c_id) x ON x.c_id = comment.c_id
  ORDER BY x.cnt DESC

Try this -

I have first queried all records from logs table which are rated 1 and are from 7 days from current date and also are ordered based on the count of c_id. Then joined this with the COmments table.

SELECT Comment.* FROM comment C
INNER JOIN (SELECT logs.c_id as c_id,count(logs.c_id) as logcount FROM logs 
WHERE logs.rated=1 
AND logs.daterate BETWEEN GETDATE() AND DATEADD(day,-7,getdate())
Group by logs.c_id
order by count(logs.c_id) desc) X 

ON C.c_id = X.c_id
ORDER BY X.logcount DESC