Maximum Count of Distinct Values in SQL

Question

please forgive me if this has been answered, but could not find it using the search tool or a basic google query.

I am trying to return a value that indicates the maximum number of rows any distinct value in a column in SQL.

For example, I'd like to use something like

SELECT MAX(COUNT(DISTINCT person_id) AS MAX_NUM_PERS_ROW
FROM mytable

and if the person with most rows in the table had 5 rows, the value returned would be 5...

Any and all help is appreciated!

Answer 1

You can do this with nested aggregation:

select max(cnt)
from (select person_id, count(*) as cnt
      from mytable
      group by person_id
     ) p;

If you actually want the person, you can also do:

select person_id, count(*) as cnt
from mytable
group by person_id
order by count(*) desc
limit 1;