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
What do you mean by top?
top ten means last 10 comments for each message
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.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.