Choose the most common value in MySQL

Question

I am looking for a way to select the most common value, for example. the person who posted the most for each thread;

SELECT MOST_OCCURRING(user_id) FROM thread_posts GROUP BY thread_id 

Is there a good way to do this?

Answer 1

If you want to count the amount for each thread, I think you can use a nested query; grouping by stream first and then by user:

 SELECT thread_id AS tid, (SELECT user_id FROM thread_posts WHERE thread_id = tid GROUP BY user_id ORDER BY COUNT(*) DESC LIMIT 0,1) AS topUser FROM thread_posts GROUP BY thread_id 

Answer 2

This will tab the user_id into the stream.

 SELECT thread_id, user_id, COUNT(*) as postings FROM thread_posts GROUP BY thread_id, user_id 

But you only want to select the top user for each thread

 SELECT thread_id, user_id, postings FROM ( SELECT thread_id, user_id, COUNT(*) as postings FROM thread_posts GROUP BY thread_id, user_id ) HAVING postings = max(postings) 

which is equivalent

 SELECT thread_id, user_id, COUNT(*) as postings FROM thread_posts GROUP BY thread_id, user_id HAVING postings = max(postings) 

The HAVING keyword is typically used with the aggregate operation for cherries — select aggregated output rows that match the conditions in the HAVING clause.

The HAVING clause is different from the WHERE clause, in which the HAVING clause filters the result of the query. Whereas the WHERE clause filters the request input. Since the HAVING clause filters the result of the query, it should appear after the ORDER BY and GROUP BY clauses.

Answer 3

There are many examples if you check questions under the tag "greatest n per group". But in this case, you don’t determine how you want to process the links — what if two or more users have the same account value?

 SELECT DISTINCT tp.thread_id, tp.user_id FROM THREAD_POSTS tp JOIN (SELECT t.thread_id, t.user_id, COUNT(t.user_id) AS occurrence, CASE WHEN @thread != t.thread_id THEN @rownum := 1 ELSE @rownum := @rownum + 1 END AS rank, @thread := t.thread_id FROM THREAD_POSTS t JOIN (SELECT @rownum := 0, @thread := -1) r GROUP BY t.thread_id, t.user_id ORDER BY t.thread_id, occurrence DESC) x ON x.thread_id = tp.thread_id AND x.user_id = tp.user_id AND x.rank = 1