Here is a table for reference:
mysql> desc foo;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| created_at | datetime | NO | | NULL | |
| updated_at | datetime | NO | | NULL | |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> desc bar;
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| foo_id | int(11) | NO | MUL | NULL | |
| status | varchar(255) | YES | | NULL | |
| created_at | datetime | NO | | NULL | |
| updated_at | datetime | NO | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
The goal is to retrieve the most recent updates for foo_id and status from the bar table, considering that there may be multiple statuses associated with a single foo_id.
An attempted query looks like this:
SELECT foo_id, status
FROM bar d1
WHERE d1.updated_at =
(SELECT MAX(d2.updated_at)
FROM bar d2
WHERE d1.foo_id = d2.foo_id)
GROUP BY foo_id
ORDER BY updated_at DESC;
However, an error was encountered:
Unhandled rejection SequelizeDatabaseError: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dev.d1.status' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
How can I successfully retrieve the latest status for each foo_id from the bar table?