Maximum Distinguished Values in SQL

Question

please forgive me if this was answered but couldn't find it with a search tool or google main query.

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

For example, I would like to use something like

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

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

Any help is appreciated!

+3

gh0strider18 08 oct. 14 at 13:34

1 answer

Gordon linoff · Accepted Answer · 2014-10-08T13:35:48+0000

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 really want this person, you can also:

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

Maximum Distinguished Values ​​in SQL