SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Limit rows linked to each joined row

    Hello,


    I've certain situation that requires certain result set from MySQL query, let's see the current query first & then ask my question:
    Code MySQL:
        			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

    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:
    1. 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.
    2. Is there any recommendations for optimizing this query (of course after solving the first point)?


    Notes:
    • 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.
    • I'm using MySQL 4.1+, but if you know the answer for another engine, just share.


    Appreciated advice in advance

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql top n per group

    I'm not sure what features mysql 4.1 supports, so you can sift through the different methods. You might end up doing a separate query to get a list of forums, and using that to build some sql to union a separate select for each forum.

  3. #3
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks.
    I've read multiple tutorials about "MySQL Top N Per Group" but unfortunately can't apply any of it on my case since I've multiple joins & multiple conditions in the same query.
    Currently I've to use PHP foreach loop to run my query multiple times to get desired results.
    I hope any one can help avoiding this loop & running multiple queries by applying "MySQL Top N Per Group" concept or by any better solution.

    Appreciated in advance

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SolidSnake@GTI View Post
    I've read multiple tutorials about "MySQL Top N Per Group" but unfortunately can't apply any of it on my case since I've multiple joins & multiple conditions in the same query.
    What you mean is you don't know how not that it can't be done. So what have you tried with writing a TOP N per GROUP that doesn't work on your query? Show us where you're getting stuck.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •