MySQL 5.7.25 group by sub-query and order by "nonaggregated column" error

Question

In short, I'm trying to order a dateset by date, and then group by another column, thus selecting the latest row of each.

Query:

SELECT name, datetime
FROM (
    SELECT *
    FROM `requests`
    ORDER BY datetime
) a
GROUP BY a.name;

Error:

#1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a.datetime' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Example table:

CREATE TABLE `requests` (
 `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(10) DEFAULT NULL,
 `datetime` datetime DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

The goal is to prevent this error from happening without having to change the default sql-mode.

After reading more about group by and only_full_group_by, I currently do not understand why the sub-query is affecting the outer query.

Query is written in accordance to https://stackoverflow.com/a/16307932/3852461

ScaisEdge · Accepted Answer · 2019-02-22T20:27:19.763

2

You should not use GROUP BY without an aggregation function like sum() or min().

Use DISTINCT if you want a distinct result

SELECT distinct name, datetime
FROM (
    SELECT *
    FROM `requests`
    ORDER BY datetime
) a

but if you need single rows for name the you should use an aggregation function for datetime eg

SELECT  name, max(datetime)
FROM (
    SELECT *
    FROM `requests`
    ORDER BY datetime
) a
group by name

edited Feb 22 '19 at 20:27

answered Feb 22 '19 at 20:10

ScaisEdge

131,976
10
91
107

So the problem wasn't with the sub-query, it was with the group by not knowing how to combine the resulting data without an aggregation function. – iautomation Feb 22 '19 at 20:20
@iautomation .. explain better your comment please . .. the subquery have not problem .. the only problem in the use of a group by clause in absence of an aggregation function in select .. – ScaisEdge Feb 22 '19 at 20:21
yes exactly. you've pointed out why the error is happening. however this answer seems to return all rows, whereas the goal is to return rows unique by name only – iautomation Feb 22 '19 at 20:25
answer updated .. with a correct approach for aggregation and group by – ScaisEdge Feb 22 '19 at 20:27
The use of the inline view is unnecessary in both of the SQL examples given, `(SELECT * FROM requests ORDER BY datetime)` could be replaced with just `requests`, to return equivalent result. Absent an ORDER BY clause on the outer query to specify the order that rows are to be returned in, MySQL is free to return the rows in any order. – spencer7593 Feb 22 '19 at 20:37

score 1 · Answer 2 · answered Feb 22 '19 at 20:25

If we want to return the latest datetime for each distinct value of name, the normative pattern would be:

 SELECT t.name
      , MAX(t.datetime) AS latest_datetime 
   FROM requests t
  GROUP
     BY t.name
  ORDER
     BY ...

If the (name,datetime) tuple is guaranteed to be unique, we can retrieve the row with with the latest time by joining the result of the query above back to the table

 SELECT r.id
      , r.name
      , r.datetime
   FROM ( SELECT t.name
               , MAX(t.datetime) AS latest_datetime 
            FROM requests t
           GROUP
              BY t.name
        ) s
  JOIN requests r
    ON r.name     <=> s.name 
   AND r.datetime <=> s.latest_datetime
 ORDER
    BY ...

If the (name,datetime) tuple is not unique, then the query above could potentially return multiple rows with the same values of name and datetime. There are approaches to handling that; given the defintion of the requests table, simplest would be to wrap the id column in an aggregate, and add a GROUP BY clause on the outer query ...

 SELECT MIN(r.id)  AS id 
      , r.name
      , r.datetime
   FROM ( SELECT t.name
               , MAX(t.datetime) AS latest_datetime 
            FROM requests t
           GROUP
              BY t.name
        ) s
  JOIN requests r
    ON r.name     <=> s.name 
   AND r.datetime <=> s.latest_datetime
 GROUP
    BY r.name
     , r.datetime
 ORDER
    BY ...

score 0 · Answer 3 · answered Feb 22 '19 at 20:15

https://www.db-fiddle.com/f/b2EAh6UiVyEdNVbEKbUEcQ/0

SELECT r.name, r.datetime
FROM `requests` r
LEFT JOIN `requests` r2 
ON r.name = r2.name
   AND r.datetime < r2.datetime
WHERE r2.name IS NULL;

or just regular GROUP BY:

SELECT r.name, MAX(r.datetime)
FROM `requests` r
GROUP BY r.name;

MySQL 5.7.25 group by sub-query and order by "nonaggregated column" error

3 Answers3