I hacked together this little query on the Data Explorer
The query finds questions where an answerer has posted two or more answers (which in itself is not necessarily a bad thing), it gives you the link to the question, information about which user posted the multiple answers, and how many answers by that user that was posted.
select question.Id as [Post Link], answer.owneruserid as [User Link], count(answer.id) as answers from Posts question join Posts answer on (answer.parentid = question.id) where answer.OwnerUserId != 0 group by answer.OwnerUserId, question.id, answer.owneruserid having count(answer.id) >= 2 order by answers desc
The last two
group by clauses seem quite useless to me in a way, as they will always only be one unique version of them after grouping by
answer.OwnerUserId. My experience from mySQL is that it simply picks a seemingly random value if not grouping by it, but in the SEDE I get this result:
Column 'Posts.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
- Is there a way to remove these extra
GROUP BY's? Or is adding these "the way it's supposed to be done"?
- How is this SQL overall, regarding performance, naming, structure, conventions and everything?
- Are there any alternative ways to rewrite this query?