0

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??

A Dolegowski
  • 73
  • 7
  • 1
    Welcome to Stack Overflow, Please [Read](https://stackoverflow.com/tour) – Arash Jun 03 '18 at 21:57
  • 1
    And see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Jun 03 '18 at 23:15
  • Possible duplicate of [SQL Server query - Selecting COUNT(\*) with DISTINCT](https://stackoverflow.com/questions/1521605/sql-server-query-selecting-count-with-distinct) – Uri Goren Dec 01 '18 at 20:17

2 Answers2

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.)

freezed
  • 1,269
  • 1
  • 17
  • 34
  • I think the OP is interested in counting distinct `rev_id`s with `COUNT(DISTINCT rev_id)`, rather than what you mentioned – Uri Goren Dec 01 '18 at 20:22
0

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

marvinscham
  • 42
  • 1
  • 5
  • Answers should be explanatory and in most cases should contain code examples. If you don't want to go into that much detail consider writing a comment instead – Dux Jun 03 '18 at 23:03
  • While true, this won't make any difference- the distinct keyword shouldn't be there either – Strawberry Jun 03 '18 at 23:14