CODEWITHSUNDEEP
sqlsql-server
psb
psb

Reputation: 55

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.

Upvotes: 2

Views: 61

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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.

Upvotes: 3

Related Questions