MySQL Forums
Forum List  »  Newbie

Re: GROUP BY error#1111 in MYSQL 4
Posted by: Rick James
Date: October 30, 2009 11:45PM

Current:
SELECT  member_id
    FROM  
    ( SELECT  m.member_id, m.group_id, m.screen_name,
              a.mescount as mescount
          FROM   exp_members m, 
            ( SELECT  member_id, COUNT(*) as mescount
                 FROM  editor_messages
                 WHERE  editor_id = 27977
                 GROUP BY  member_id) AS a
        WHERE  a.member_id = m.member_id
      UNION 
      SELECT  member_id, group_id, screen_name,
              0
          FROM  exp_members
    ) as m
    WHERE  screen_name LIKE '%%'
      AND  group_id != 2
    GROUP BY  member_id
    ORDER BY  MAX(mescount) DESC,
        CASE
          WHEN group_id = 1 then 5
          WHEN group_id = 5 then 4
          WHEN group_id = 6 then 2
          WHEN group_id = 9 then 3
          WHEN group_id = 14 then 1
          WHEN group_id = 15 then 6
          ELSE 7 END ASC,
        screen_name ASC
    LIMIT  10
* Use tmp table (CREATE TEMPORARY TABLE ...) for the subqueries.
* Create an ordering table for the CASE. It would have pairs (1,5), (5,4), etc. Then JOIN to it to get the desired ordering.

Options: ReplyQuote


Subject
Written By
Posted
October 27, 2009 08:18AM
Re: GROUP BY error#1111 in MYSQL 4
October 30, 2009 11:45PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.