StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL: Limit rows linked to each joined row
primarykey
Id
2649596
data
AcceptedAnswerId
2702231
AnswerCount
1
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2010-04-15T23:07:21.737
FavoriteCount
0
LastActivityDate
2010-04-23T22:02:30.343
LastEditDate
2010-04-16T11:52:44.077
LastEditorUserId
65223
OwnerUserId
307536
ParentId
0
PostTypeId
1
Score
2
ViewCount
4405
LastEditorDisplayName
text
Body
I've certain situation that requires certain result set from MySQL query, let's see the current query first & then ask my question: <pre><code>SELECT thread.dateline AS tdateline, post.dateline AS pdateline, MIN(post.dateline) FROM thread AS thread LEFT JOIN post AS post ON(thread.threadid = post.threadid) LEFT JOIN forum AS forum ON(thread.forumid = forum.forumid) WHERE post.postid != thread.firstpostid AND thread.open = 1 AND thread.visible = 1 AND thread.replycount >= 1 AND post.visible = 1 AND (forum.options & 1) AND (forum.options & 2) AND (forum.options & 4) AND forum.forumid IN(1,2,3) GROUP BY post.threadid ORDER BY tdateline DESC, pdateline ASC </code></pre> As you can see, mainly I need to select dateline of threads from 'thread' table, in addition to dateline of the second post of each thread, that's all under the conditions you see in the WHERE CLAUSE. Since each thread has many posts, and I need only one result per thread, I've used GROUP BY CLAUSE for that purpose. This query will return only one post's dateline with it's related unique thread. My questions are: <ol> <li>How to limit returned threads per each forum!? Suppose I need only 5 threads -as a maximum- to be returned for each forum declared in the WHERE CLAUSE 'forum.forumid IN(1,2,3)', how can this be achieved.</li> <li>Is there any recommendations for optimizing this query (of course after solving the first point)?</li> </ol> Notes: <ul> <li>I prefer not to use sub-queries, but if it's the only solution available I'll accept it. Double queries not recommended. I'm sure there's a smart solution for this situation.</li> <li>I'm using MySQL 4.1+, but if you know the answer for another engine, just share.</li> </ul> Appreciated advice in advance :)
Tags
<sql><mysql><join><limit>
Title
SQL: Limit rows linked to each joined row
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USKM.
UserOwnerUserId
1. USOmranic
plurals
PostLinksPostIdRelatedPostId
1. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
PostLinksRelatedPostIdPostId
1. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POSQL: Limit rows linked to each joined row
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.