SELECT person
FROM person
INNER JOIN collectionmember ON sourceobjectid = personid
GROUP BY sourceobjectid
HAVING COUNT(sourceobjectid) > 1;
It is said that if we use GROUP BY clause, the GROUP BY column name must be there in the SELECT column list. But the above query works without selecting sourceobjectid.
How this query works?
The results is not the first row encountered. MySQL is quite clear about the use of so-called "hidden columns". To quote from the documentation:
MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.
The intention of this extension is to allow queries where you group on the primary key of a table, and not have to put in all the additional columns. This behavior is actually consistent with the ANSI standard.
The use of "hidden columns" in other cases is suspect. The values chosen are arbitrary. MySQL does not even guarantee that they come from the same row (although in practice, they do).