Get Most Recurring Value Within Grouped Results MySQL version 5.7-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

If performance is not a concern you can use a simple correlated subquery for this:

select usersection, usersubsection, (
    select category
    from t as x
    where usersection = t.usersection and usersubsection = t.usersubsection
    group by category
    order by count(*) desc
    limit 1
) as preferedcategory
from t
group by usersection, usersubsection

Demo on db<>fiddle