psb psb - 1 year ago 83
SQL Question

SQL Get highest repeating value for a group clause

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:

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.

vkp vkp
Answer Source

Use rank to number the id's based on their value counts in descending order and pick up the 1st ranked rows.

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download