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.
我试图返回一个值，该值指示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...
如果表中有大多数行的人有5行，那么返回的值将是5……
Any and all help is appreciated!
感谢您的帮助!
1 个解决方案
#1
6
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;
#1
6
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;