Bug #20024 Single Column Distict Query not performing as expected
Submitted: 23 May 2006 16:36 Modified: 23 May 2006 22:19
Reporter: Michael Furdyk Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.19 OS:Linux (RHEL4)
Assigned to: CPU Architecture:Any

[23 May 2006 16:36] Michael Furdyk 
Description:
We are trying to query the latest 3 items from a table from a distinct UserID. When we query without a distinct UserID, the latest submissions come up properly. When we try this code suggested in the MySQL documentation comments for SELECT statements, it seems to group earlier queries with the newer ones and doesn't show the latest items:

SELECT Username, MemberID, GGsID, WorkTitle, Thumbname, Submitted
FROM tig.ggSubmissions
LEFT JOIN tig.Users ON Users.UserID = ggSubmissions.MemberID
WHERE ggSubmissions.Thumbname != ''
AND ggSubmissions.MemberID != '0'
AND ggSubmissions.approved = '2'
AND ggSubmissions.flTIGed <=1
AND ggSubmissions.Submitted != '0000-00-00'
GROUP BY MemberID
HAVING COUNT( MemberID ) >=1
ORDER BY Submitted DESC
LIMIT 3

How to repeat:
Check grouping on single columns with multiple dated items from a single userid. Does the oldest or newest record get grouped?

Suggested fix:
Is there a way to do this - or is it simply not possible with the way GROUP BY is processed. Or is this producing incorrect results?
[23 May 2006 22:19] Hartmut Holzgraefe 
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

See http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html

   In standard SQL, you would have to add customer.name to the GROUP BY clause. 
   In MySQL, the name is redundant.

   Do not use this feature if the columns you omit from the GROUP BY part are not 
   constant in the group. The server is free to return any value from the group, 
   so the results are indeterminate unless all values are the same.