CODEWITHSUNDEEP
databasemysqlleft-joinjoin
Arun SS
Arun SS

Reputation: 1911

top 10 comments of each messages

I have two tables. One for messages and one for comments. For each entry in messages table there will be multiple records in comments table. Now I want to select the top 5 messages and top 10 comments for each message. My table structure is

Messages

MID Message

1 msg 1

2 msg 2

Comments

CID MID Comment Date

1 1 Cmnt1 date 1

2 1 Cmnt2 date 2

3 1 Cmnt3 date 3

4 2 Cmnt4 date 4

5 2 Cmnt5 date 5

Thanks in Advance

-Arun

Upvotes: 0

Views: 144

Answers (1)

Jules
Jules

Reputation: 1371

You're question is a bit vague. However, assuming you have a field indicating your criteria for being "top", e.g. votes:

For MySQL you can use a select statement like this:

SELECT * 
FROM comments
JOIN messages ON comments.mid = messages.mid
WHERE messages.mid = 1    
ORDER BY comments.topvotes DESC
LIMIT 10;

This query would give you the top 10 voted comments for message 1.

A more complex query for getting the comments sorted for the top five messages:

SELECT comments.cid
FROM comments
JOIN messages ON comments.mid = messages.mid
WHERE comments.mid IN(
        SELECT messages.mid
        FROM messages
        ORDER BY messages.votes DESC
        LIMIT 5)
ORDER BY messages.mid, comments.votes DESC;

But I can't quite figure out, how to limit this to the 10 top comments. I would use a cursor, but I've been doing too much PLSQL on Oracle lately anyway. So if anyone got a good solution how to express that in a single select with a subquery, please shoot...

Upvotes: 1

Related Questions