Using GROUP BY with MAX()/MIN() giving bad results

Question

The table

The query

SELECT
    id, MAX(fecha_hora_carga) AS fecha_hora_carga
FROM
    calibraciones_instrumentos
GROUP BY
    instrumento_id

The result

Its returning the most recent fecha_hora_carga dates, but the ids are 24 and 28...i think they should be 27 and 29!

Why are the ids not corresponding with the date?

I wrote an explanation here: https://stackoverflow.com/a/13999903/20860 — Bill Karwin, Jan 31 '20 at 20:01

score 3 · Answer 1 · edited Jan 31 '20 at 22:37

The problem is MySQL does not make much sense when grouping by a max value. It grabs the max column and then the other columns in that table you selected by whatever order you sort them by.

To get what you want, you have to use subqueries to pull the data you want. Here is an example:

SELECT
    t1.id,
    t1.fecha_hora_carga
FROM
    calibraciones_instrumentos AS t1
JOIN(
    SELECT MAX(fecha_hora_carga) AS fecha_hora_carga,
        instrument_id
    FROM
        calibraciones_instrumentos
    GROUP BY
        instrument_id
) AS t2
ON  (t1.fecha_hora_carga = t2.fecha_hora_carga AND
     t1.instrument_id = t2.instrument_id
    );

Gordon Linoff · Answer 2 · 2020-02-01T00:14:09.903

2

Because you are misusing SQL. You have one column in the GROUP BY clause and that column isn't even being selected!

In most databases -- including the most recent versions of MySQL -- your query would generate a syntax error because id is neither in the GROUP BY nor an argument to an aggregation function such as MIN().

So, MySQL is providing just an arbitrary id. I would expect an aggregation query to look like this:

SELECT instrumento_id, MAX(fecha_hora_carga) AS fecha_hora_carga
FROM calibraciones_instrumentos
GROUP BY instrumento_id;

Or, if you want the row with the maximum fecha_hora_carga for each instrumento_id, use filtering:

select ci.*
from calibraciones_instrumentos ci
where ci.fecha_hora_carga = (select max(ci2.fecha_hora_carga)
                             from calibraciones_instrumentos ci2
                             where ci2.instrumento_id = ci.instrumento_id
                            );

edited Feb 01 '20 at 00:14

answered Jan 31 '20 at 18:54

Gordon Linoff

1,242,037
58
646
786

Your query should not work at all if you are `SELECTing` any column that is not in `GROUP BY`. I don't know why the query in the OP is accepted. – Mike Robinson Jan 31 '20 at 18:56
@MikeRobinson . . . Your comment does not apply to the queries in this answer. I assume it is directed to the OP. If so, it should probably be on the *question*. – Gordon Linoff Jan 31 '20 at 18:58
Did you mean to use `SELECT MAX(ci2.fecha_hora_carga)...` in the second query? – Bill Karwin Jan 31 '20 at 20:00
Please Gordon, edit the second query in your answer considering Bill's suggestion so that i can mark it as accepted. Thanks! – Hernan Jan 31 '20 at 20:20

score 1 · Answer 3 · answered Jan 31 '20 at 20:07

This is because your query is incorrect

The MAX is an aggregate function and gets the max. value from the fecha_hora_carga, this won't give you the corresponding id too it just gets the maximum value stored in the fecha_hora_carga column, not a row.

See the following sample:

mysql>CREATE TABLE test_group_by (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, val1 INT, val2 INT);`

mysql>INSERT INTO test_group_by (val1, val2) VALUES(10,1), (6, 1), (18, 1), (22, 2), (4, 2);

mysql> SELECT * FROM test_group_by;
+----+------+------+
| id | val1 | val2 |
+----+------+------+
|  1 |   10 |    1 |
|  2 |    6 |    1 |
|  3 |   18 |    1 |
|  4 |   22 |    2 |
|  5 |    4 |    2 |
+----+------+------+

mysql> SELECT id, MAX(val1) FROM test_group_by GROUP BY val2;
+----+-----------+
| id | MAX(val1) |
+----+-----------+
|  1 |        18 |
|  4 |        22 |
+----+-----------+

As you can see in the example, that is a simplified representation of your table. The MAX function does not retrieves a entry, just the max. value of all the entries in the table. But your query also asks for a ID, it just makes one up (which ID is returned cannot be said for sure).

Using GROUP BY with MAX()/MIN() giving bad results

Why are the ids not corresponding with the date?

3 Answers3