On Sat, May 3, 2008 at 6:00 PM, Patrick Aljord <[EMAIL PROTECTED]> wrote: > SELECT count(*) as counted, > u.login > FROM posts c > left join users u on posts.poster_id=u.id > group by c.user_id > having counted>1 > order by counted DESC > LIMIT 20
This is a bad query. You are abusing GROUP BY. Only use GROUP BY if if your SELECT clause is going to return the results of an aggregating functions or what you are grouping by. If you need more info throw the GROUP BYed query in a derived table. What you are doing is an illegal query in most databases, and will return random results. Your doing a left join which can increase the number of rows returned. This is then GROUP BYed and run through a HAVING. Is: posts.poster_id=users.id a one to one relationship? If it is not, then count(*) would be a larger number and pass the HAVING. This may not be your problem, but I suggest you have more than you realize. I suggest reading http://www.xaprb.com/blog/2006/04/26/sql-coding-standards/ (because what you posted violated several of the suggestions of that very good post, and was therefore less easy to read) and the post it links to on 'how to group data correctly in SQL'. I have a good idea on how to solve your problem using your current syntax (because for a while I worked without anyone telling me that what I was doing was wrong), but I am not going to tell you because you should be writing valid SQL. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
