fab fab - 4 years ago 148
SQL Question

MySQL select user who less performed an action

I have the following table structures:

ACTIONS

+ userid + action +
+----------+----------+

USERS

+ id + name +
+----------+----------+
+ 1 + james +
+----------+----------+
+ 2 + john +
+----------+----------+


A data example:

ACTIONS

+ userid + action +
+----------+----------+
+ null + action1 +
+----------+----------+
+ 1 + action2 +
+----------+----------+
+ 1 + action3 +
+----------+----------+
+ 2 + action4 +
+----------+----------+


I need a SELECT query that will return the user who performed less actions.

If all fields are null (the first launch) or all equal (all users performed the same action), it can return 1 user (rand, asc, desc, it's the same).

Answer Source

You'll want to use GROUP BY. We can then ORDER BY the number of actions they performed.

To find the one with the lowest number, just use LIMIT 1

SELECT user_id FROM users
INNER JOIN data ON users.id = data.users_id
GROUP BY action
ORDER BY COUNT(action)
LIMIT 1;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download