Hello All,

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


MID Message
1 msg 1
2 msg 2

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

6 Years
Discussion Span
Last Post by smantscheff

Since MySQL does not allow LIMIT clauses in subqueries, this is no trivial task. If you can tackle the problem on the client side in a procedural language, it's not a problem. But if you want to have exactly one query, it gets tricky.
I remember dimly to have solved such a problem using a comparison with the count of records which have an ID bigger than the current one and selecting only those where this count was 0 or below my LIMIT target.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.