I want a SQL query which should tell me that for each ID which value repeated most of time. For example lets take the following table:
我需要一个SQL查询，它应该告诉我，对于每个ID，哪个值重复的时间最多。例如，我们来看下表:
Id Value
1 10
1 20
1 10
1 10
2 1
1 3
Desired Output
期望输出值
Id Value Count
1 10 3
2 1 1
From above example, it shows that for Id 1, Value 10 was repeated most of times and for Id 2, value 1 was repeated most of times Any suggestion would be really appreciated.
从上面的例子可以看出，对于Id 1，值10重复的次数最多，对于Id 2，值1重复的次数最多，任何建议都是值得感激的。
1 个解决方案
#1
3
Use rank to number the id's based on their value counts in descending order and pick up the 1st ranked rows.
使用rank来编号，根据它们的值按降序排列，并获取第一个排序的行。
select id, value, cnt
from (select id, value, count(*) as cnt,
rank() over (partition by id order by count(*) desc) as rnk
from t
group by id, value) x
where rnk = 1
Based on Gordon's comment, if you need only one value per id in case of ties, use row_number instead of rank, as rank returns all the ties in value counts.
根据Gordon的注释，如果您需要每个id只有一个值，而不使用rank而使用row_number，因为rank返回值计数中的所有领带。
#1
3
Use rank to number the id's based on their value counts in descending order and pick up the 1st ranked rows.
使用rank来编号，根据它们的值按降序排列，并获取第一个排序的行。
select id, value, cnt
from (select id, value, count(*) as cnt,
rank() over (partition by id order by count(*) desc) as rnk
from t
group by id, value) x
where rnk = 1
Based on Gordon's comment, if you need only one value per id in case of ties, use row_number instead of rank, as rank returns all the ties in value counts.
根据Gordon的注释，如果您需要每个id只有一个值，而不使用rank而使用row_number，因为rank返回值计数中的所有领带。