MySql error: incompatible with sql_mode=only_full_group_by

Question

I've inherited a CodeIgniter query that generates this SQL

EXAMPLE:

SELECT `users`.`id`, `users`.`username`, `users`.`email`, `users`.`photo`, `users`.`rating`
FROM `pool_details`
JOIN `users` ON `users`.`id` = `pool_details`.`captain_id`
WHERE `pool_details`.`pool_type` =0
AND `pool_details`.`pool_close` > '2020-01-02 18:39:42'
GROUP BY `pool_details`.`captain_id`
ORDER BY `pool_details`.`members_count` DESC
LIMIT 20

ERROR - 2020-01-02 18:39:42 --> Query error: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'pool_details.members_count' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by - Invalid query:

Here is the same data with some "extra columns" and the offending clauses removed:

SELECT users.id, users.username, users.email,users.rating,pool_details.members_count,pool_details.pool_type,pool_details.pool_close
FROM  pool_details
JOIN  users ON users.id = pool_details.captain_id
WHERE pool_details.pool_type = 0 AND pool_details.pool_close > '2020-01-02 18:39:42'
ORDER BY pool_details.members_count DESC;
//GROUP BY `pool_details`.`captain_id`
+----+----------+--------------------------+--------+---------------+-----------+---------------------+
| id | username | email                    | rating | members_count | pool_type | pool_close          |
+----+----------+--------------------------+--------+---------------+-----------+---------------------+
| 13 | Ronaldo  | [email protected] | 4.6    |           100 | 0         | 2020-01-04 03:00:00 |
| 13 | Ronaldo  | [email protected] | 4.6    |           100 | 0         | 2020-01-03 23:30:00 |
| 13 | Ronaldo  | [email protected] | 4.6    |           100 | 0         
...
| 13 | Ronaldo  | [email protected] | 4.6    |             0 | 0         | 2020-01-03 00:00:00 |
| 13 | Ronaldo  | [email protected] | 4.6    |             0 | 0         | 2020-01-03 00:30:00 |
| 13 | Ronaldo  | [email protected] | 4.6    |             0 | 0         | 2020-01-04 21:30:00 |
| 13 | Ronaldo  | [email protected] | 4.6    |             0 | 0         | 2020-01-03 03:00:00 |
+----+----------+--------------------------+--------+---------------+-----------+---------------------+
28 rows in set (0.00 sec)

What I want is to:

1. Show User's username, email and rating
2. Order by "members_count"
3. Show the user only once

For example:

SELECT DISTINCT users.id, users.username, users.email,users.rating
FROM  pool_details
JOIN  users ON users.id = pool_details.captain_id
WHERE pool_details.pool_type = 0 AND pool_details.pool_close > '2020-01-02 18:39:42';
+----+----------+--------------------------+--------+
| id | username | email                    | rating |
+----+----------+--------------------------+--------+
|  5 | wheel    | [email protected]         | NULL   |
| 13 | Ronaldo  | [email protected] | 4.6    |
+----+----------+--------------------------+--------+
2 rows in set (0.00 sec)
<= This shows the users individually ... but it's *NOT* ordered by "members_count".

Q: Is there any combination of "GROUP BY" and/or "DISTINCT" that I can use with mySql 5.7 that will give me the result set I need?

Answer 1

Other than the ORDER BY, you seem to just want EXISTS. That said, you can use another subquery in the ORDER BY:

SELECT u.*
FROM users u
WHERE EXISTS (SELECT 1
              FROM pool_details pd
              WHERE pd.captain_id = u.id AND
                    pd.pool_type = 0 AND
                    pd.pool_close > '2020-01-02 18:39:42'
             )
ORDER BY (SELECT MAX(pd2.members_count)
          FROM pool_details pd2
          WHERE pd2.captain_id = u.id AND
                pd2.pool_type = 0 AND
                pd2.pool_close > '2020-01-02 18:39:42'
         ) DESC
LIMIT 20

EDIT:

You can also write this as:

SELECT u.*,
       (SELECT MAX(pd2.members_count)
        FROM pool_details pd2
        WHERE pd2.captain_id = u.id AND
              pd2.pool_type = 0 AND
              pd2.pool_close > '2020-01-02 18:39:42'
      ) as max_members_count
FROM users u
HAVING max_member_count IS NOT NULL
ORDER BY max_member_count DESC
LIMIT 20

Or:

SELECT u.*
FROM users u JOIN
     (SELECT pd2.captain_id, MAX(pd2.members_count) as max_member_count
      FROM pool_details pd2
      WHERE pd2.pool_type = 0 AND
            pd2.pool_close > '2020-01-02 18:39:42'
     ) pd
     ON pd.captain_id = u.id
ORDER BY max_member_count DESC
LIMIT 20l

Answer 2

Problem:

A PHP/CodeIgniter application I inherited generated this SQL:

SELECT `users`.`id`, `users`.`username`, `users`.`email`, `users`.`photo`, `users`.`rating`
FROM `pool_details`
JOIN `users` ON `users`.`id` = `pool_details`.`captain_id`
WHERE `pool_details`.`pool_type` =0
AND `pool_details`.`pool_close` > '2020-01-02 18:39:42'
GROUP BY `pool_details`.`captain_id`
ORDER BY `pool_details`.`members_count` DESC
LIMIT 20

.. which, in turn, caused this MySql error:

ERROR - 2020-01-02 18:39:42 --> Query error: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'pool_details.members_count' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by - Invalid query:

Cause:

forpas asked the right question:

Which "members_count" of all that a user may have do you want to use in ORDER BY?

spencer7593 gave the right solution:

If we know that members_count is functionally dependent ... then we could just wrap in an aggregate function. .e.g ORDER BY MAX(pool_details.members_count) DESC.

Solution:

Modified PHP/CodeIgniter query:

$this->db->select('users.id,users.username,users.email,users.photo,users.rating');
$this->db->from('pool_details');
$this->db->join('users','users.id = pool_details.captain_id');
$this->db->where('pool_details.pool_type',0);
$this->db->where('pool_details.pool_close >',$now);
$this->db->order_by('MAX(pool_details.members_count)','DESC'); // <-- Added "MAX()"
$this->db->group_by('pool_details.captain_id');
$this->db->limit(20, 0);
$query = $this->db->get()->result();

Discussion: Each "Pool" has a "Captain".

We want to "group by" each individual captain among all the captains in all the pools.

The top of the list should be the pool with the most members.

The aggregate MAX(pool_details.members_count) gives us exactly the result we're looking for.

There's no "functional dependency"; the value of "members_count column" from all rows in a collapsed group are typically NOT the same. Nevertheless, "MAX()" does exactly what we need: it resolves the error, and we get the results we want from the schema we have.