SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Dec 2002
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    plz help me, DISTINCT or GROUP BY

    for some reason I can't get this to work, and I'm oblivious to why it refuses to cooperate:

    Here is a MySQL query. All it is doing is taking info about a forum and listing it:

    Code:
    // =======================
    // Get list of all forums
    // =======================
    $DB->query('SELECT  t.lastpost_id AS uid,
    f.fid AS fid,
    t.lastpost AS lastpost,
    t.title AS title,
    t.lastpost_id AS uid,
    
    
    f.name AS name,
    f.description AS description,
    f.cid AS cid,
    
    u.username AS username
    
    FROM w_forum f
    
    LEFT JOIN w_topics t
    ON t.fid = f.fid
    
    LEFT JOIN w_users u
    ON u.uid = t.lastpost_id
    
    WHERE f.canview = "" OR f.canview LIKE "%g' . $User->u['gid'] . ',%" OR f.canview LIKE "%u' . $User->u['uid'] . ',%"
    
    ORDER BY f.name ASC, t.lastpost DESC');
    Now this results in:

    http://deadair.easywebhome.net/Image1.gif
    (Sorry copy and paste into a new browser window, my host stops it for some reason)





    Now when I add in the GROUP BY(f.fid):
    Code:
    $DB->query('SELECT  t.lastpost_id AS uid,
    f.fid AS fid,
    t.lastpost AS lastpost,
    t.title AS title,
    t.lastpost_id AS uid,
    
    
    f.name AS name,
    f.description AS description,
    f.cid AS cid,
    
    u.username AS username
    
    FROM w_forum f
    
    LEFT JOIN w_topics t
    ON t.fid = f.fid
    
    LEFT JOIN w_users u
    ON u.uid = t.lastpost_id
    
    WHERE f.canview = "" OR f.canview LIKE "%g' . $User->u['gid'] . ',%" OR f.canview LIKE "%u' . $User->u['uid'] . ',%"
    
    GROUP BY (f.fid)
    
    ORDER BY f.name ASC, t.lastpost DESC');
    This happens:
    http://deadair.easywebhome.net/Image2.gif



    the green ones should show up, and are originally in the right order, but then for some reason it all messes up and the red ones show up


    plz can somebody help me

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What happens if you change
    FROM w_forum f
    LEFT JOIN w_topics t
    ON t.fid = f.fid
    LEFT JOIN w_users u
    ON u.uid = t.lastpost_id
    to
    FROM w_forum f
    LEFT JOIN w_topics t
    ON t.fid = f.fid
    INNER JOIN w_users u
    ON u.uid = t.lastpost_id

  3. #3
    SitePoint Member
    Join Date
    Dec 2002
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the same thing happens

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    make sure all non-aggregate columns in the SELECT list are part of the grouping in the GROUP BY, and vice versa

    you have

    SELECT A,B,C,D,E,F,G,H,I
    FROM ...
    GROUP BY B

    and it should be

    SELECT A,B,C,D,E,F,G,H,I
    FROM ...
    GROUP BY A,B,C,D,E,F,G,H,I

    try adding a COUNT(*) or similar aggregate function to see the effect

    rudy
    http://rudy.ca/

  5. #5
    SitePoint Member
    Join Date
    Dec 2002
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by r937
    make sure all non-aggregate columns in the SELECT list are part of the grouping in the GROUP BY, and vice versa

    you have

    SELECT A,B,C,D,E,F,G,H,I
    FROM ...
    GROUP BY B

    and it should be

    SELECT A,B,C,D,E,F,G,H,I
    FROM ...
    GROUP BY A,B,C,D,E,F,G,H,I

    try adding a COUNT(*) or similar aggregate function to see the effect

    rudy
    http://rudy.ca/
    i'm not quite sure I understand I need to use group by f.fid otherwise each forum will show up more than once, if I use group by anything else it doesn't work for me

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    then you may need to do some research on grouping

    grouping is intended to collapse a number of detail records from the query retrieval logic, into one record per group in the result

    usually, there is some kind of aggregate function, like COUNT() or SUM(), when grouping

    now, if each forum has multiple topics, then i can understand grouping by forum, and, say, counting the number of topics per group

    but you do not seem to want that, because you have the topic title in the SELECT list

    in other words, it lists multiple topics per forum, so grouping on forum doesn't make sense

    maybe you can explain what you were trying to do?

    rudy

  7. #7
    SitePoint Member
    Join Date
    Dec 2002
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    basically it shows a list of forums and the last post in each of them. I'm currently doing it by having a "lastpost_id" for each forum that's updated for each post.

    But it requires an extra update query every time a post is made/edited/deleted

    I thought this method would be more efficient, and actually easier to implement. Looks like I was wrong heh. It works in theory just not in practise.

  8. #8
    SitePoint Member
    Join Date
    Dec 2002
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I understand the logic as to why it isn't working now.

    It needs to sort the topics first into order of when the last post was made DESC, and then attach it by forum ID (fid)


    instead it attaches it via the post closest to the top of the table by fid, and then the query sorts the results.

    I really can't see there being a method around this.


  9. #9
    SitePoint Member
    Join Date
    Dec 2002
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok after a bit more messing about, I tried using the select table as Topics, but obviously if there's no topics then the forum isn't displayed.

    I'm determined to get this to work now lol

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, it's a little clearer now what you wanted

    unfortunately, because mysql doesn't support subqueries, getting the latest post for each forum in one query is not possible

    the mysql.com site itself recommends a temp table -- see 3.5.4 The Rows Holding the Group-wise Maximum of a Certain Field

    you should not need to update a "lastpost_id" column on every post


    rudy


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
  •