MySQL - Get the max count from a subquery group

Question

I have a table logins with the following schema:

| id | user_id | weekday |
|----|---------|---------|
|  1 |       1 |       1 |
|  2 |       1 |       2 |
|  3 |       1 |       2 |

...

Weekday is a number from 0 to 6.

I want to get which weekday has the highest count, for each user_id in the table.

I tried the following query:

SELECT MAX(num) as max_num, user_id, weekday
FROM (
    SELECT COUNT(*) as num, user_id, weekday
    FROM logins
    GROUP BY user_id, weekday
) C
WHERE user_id = C.user_id AND num = C.num
GROUP BY user_id;

Which gets me weekday = 1 instead of 2. I think that I shouldn't use a WHERE clause here, but I couldn't manage to get the correct result.

I've checked other similar questions with no luck, such as:

I created a SQL Fiddle with my example: http://sqlfiddle.com/#!9/e43a71/1

score 1 · Accepted Answer · answered Aug 05 '17 at 15:56

1

Here is a method:

SELECT user_id, MAX(num) as max_num, 
       SUBSTRING_INDEX(GROUP_CONCAT(weekday ORDER BY num DESC), ',', 1) as weekday_max
FROM (SELECT user_id, weekday, COUNT(*) as num
      FROM logins l
      GROUP BY user_id, weekday
     ) uw
GROUP BY user_id;

answered Aug 05 '17 at 15:56

Gordon Linoff

1,242,037
58
646
786

Note that `count(*)` may not always work perfectly in InnoDB due to a bug that is just now fixed in MySQL 5.7.19. I had this problem earlier. Seems to be working ok after upgrading to 5.7.19. https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-19.html – Allen King Aug 05 '17 at 16:14
@Marc . . . You really prefer the other answer to this one? – Gordon Linoff Aug 06 '17 at 00:48

Taras Bogach · Answer 2 · 2017-08-05T18:32:27.163

SELECT days.user_id, days.weekday, days.num
FROM (
    SELECT user_id, MAX(num) AS num
    FROM (
      SELECT user_id, weekday, COUNT(*) AS num
      FROM logins
      GROUP BY user_id, weekday
    ) max
    GROUP BY user_id
) nums
JOIN (
    SELECT user_id, weekday, COUNT(*) as num
    FROM logins
    GROUP BY user_id, weekday
) days ON(days.user_id = nums.user_id AND days.num = nums.num);

-- With Mariadb 10.2 or MySQL 8.0.2
WITH days AS (
    SELECT user_id, weekday, COUNT(*) as num
    FROM logins
    GROUP BY user_id, weekday
)
SELECT days.user_id, days.weekday, days.num
FROM (
    SELECT user_id, MAX(num) AS num
    FROM days
    GROUP BY user_id
) nums
JOIN days ON(days.user_id = nums.user_id AND days.num = nums.num);

If you have Mariadb 10.2, or MySQL 8.0.2+ you can write better and shorter query using window function, or WITH statment. — Taras Bogach, Aug 05 '17 at 18:27

MySQL - Get the max count from a subquery group

2 Answers2