MySQL Distinct guidance needed

Question

My Goal: Display the names of the top 10 users who have submitted the maximum number of revision in one changeid. (A change can have multiple revisions)

Select DISTINCT(rev_authorUsername), COUNT(rev_id) as revnum FROM t_revision
group by rev_authorUsername, rev_changeId
 order by revnum desc
 limit 10;

I get the top 10 authors but I get duplicate names. Any help pls??

Answer 1

1. DISTINCT modifiers applys for the selected set, do not use parenthesis around rev_authorUsername
2. Using DISTINCT and GROUP_BY (here with different columns) may have some unexpected result:

(…) cavalier treatment of "Group By" and "Distinct" could lead to some pernicious gotcha's down the line if you're not careful.

What about this request:

SELECT DISTINCT rev_authorUsername, COUNT(rev_id) as revnum
FROM t_revision
ORDER BY revnum DESC
LIMIT 10;

(The use of DISTINCT & GROUP BY is explained in the MySQL Reference Manual - SELECT Syntax.)

Answer 2

Try removing the brackets surrounding rev_authorUsername after DISTINCT, they shouldn't be there and might be what's messing with your results.