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 Count
1 10 3
2 1 1
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 returns all the ties in value counts.