0

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

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

3
Contributors
3
Replies
4
Views
6 Years
Discussion Span
Last Post by smantscheff
0

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.