How to select most frequent value in a column per each id group?
You can use a window function to rank the userids based on their count of data1.
WITH cte AS (SELECT user_id , data1 , ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY COUNT(data1) DESC) rnFROM dbo.YourTableGROUP BY user_id, data1)SELECT user_id, data1FROM cte WHERE rn = 1
If you use proper "order by" then distinct on (user_id) make the same work because it takes 1.line from data partitioned by "user_id". DISTINCT ON is specialty of PostgreSQL.
select distinct on (user_id) user_id, most_frequent_value from (SELECT user_id, data1 AS most_frequent_value, count(*) as _countFROM my_tableGROUP BY user_id, data1) aORDER BY user_id, _count DESC
With postgres 9.4 or greater it is possible. You can use it like:
SELECT user_id, MODE() WITHIN GROUP (ORDER BY value) FROM (VALUES (0,6), (0,6), (0, 6), (0,1),(0,1), (1,5), (1,5), (1,3), (1,3), (1,7)) users (user_id, value)GROUP BY user_id