CODEWITHSUNDEEP
mysqlsqlsubquery
tommyd456
tommyd456

Reputation: 10693

MySQL EXISTS and ORDER BY

I have two tables 'Comments' and 'Likes' and I can show all the comments that have been liked and order them by when the comments where added. What I can't seem to do at the moment is order the comments according to when they were liked.

This is what I have at the moment:

SELECT * 
FROM comments AS c 
WHERE EXISTS (
    SELECT * 
    FROM likes AS l 
    WHERE c.commentID=l.commentID)

Could anyone help me with the SQL to show the comments in order with the one that was most recently liked first and so on...

Just to add - I only want to show the comment once and avoid showing any comments that have not been liked.

Upvotes: 0

Views: 1895

Answers (3)

jmilloy
jmilloy

Reputation: 8365

You want to join the tables.

SELECT comments.*
FROM comments JOIN likes ON comments.commentID = likes.commentID
GROUP BY comments.commentID
ORDER BY MAX(likes.date) DESC;

The JOIN makes rows with all of the fields from comments and likes. If you use LEFT JOIN it will include comments that have not been liked, but using a plain JOIN should do what you want.

The GROUP BY collapses rows so you only have one per comment.

The ORDER BY orders the rows by the like date. I used MAX(likes.date) because you will have potentially many like dates for each comment, and you want to choose a specific one. You could try MIN(likes.date) as well, depending on what you're looking for (most recently liked vs first liked).

Upvotes: 4

emco
emco

Reputation: 4719

Since you only want to show the comments that have like, you can do this:

SELECT * FROM comments INNER JOIN likes USING(commentID) ORDER BY like_date DESC;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you have multiple likes for a given comment, then you need an aggregation, such as:

SELECT c.*
FROM comments c join
     (select l.commentId, MIN(likedate) as FirstLikeDate, MAX(likedate) as MaxLikeDate
      from likes l
      group by l.commentId
     ) l
     on c.commentId = l.CommentId
order by MaxLikeDate desc

Upvotes: 3

Related Questions