I would like to select a user's preferred category based on occurrences within a database.
I am using MySQL version 5.7. I tried the following example based on another question but the results were not as expected.
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
My table establishes a single user by two columns User Section and User Sub Section.
User Section | User Sub Section | Category
------------------------------------------
1 | A | Foo
1 | A | Bar
1 | A | Foo
1 | B | 123
2 | A | Bar
2 | A | Bar
2 | A | Bar
2 | A | Foo
3 | A | 123
3 | A | 123
3 | B | Bar
4 | A | Foo
Expected Results
User Section | User Sub Section | Category
------------------------------------------
1 | A | Foo
1 | B | 123
2 | A | Bar
3 | A | 123
3 | B | Bar
4 | A | Foo
I'm using MySQL version 5.7. I was advised to delete my previous question and repost stating the MySQL version number, as a lot of functionality that exists in MySQL 8 doesn't exist in previous versions.
This is a hairy problem to handle on MySQL 5.7. Here is one approach:
SELECT s1.section, s1.sub_section, s1.category
FROM
(
SELECT section, sub_section, category, COUNT(*) AS cnt
FROM yourTable
GROUP BY section, sub_section, category
) s1
INNER JOIN
(
SELECT section, sub_section, MAX(cnt) AS max_cnt
FROM
(
SELECT section, sub_section, category, COUNT(*) AS cnt
FROM yourTable
GROUP BY section, sub_section, category
) t
GROUP BY section, sub_section
) s2
ON s2.section = s1.section AND
s2.sub_section = s1.sub_section AND
s1.cnt = s2.max_cnt
ORDER BY s1.section, s1.sub_section, s1.category;
Here is a running demo.