Mysql - Group by and select max value

Question

I have the following tables:

reservas_tanatosalas

I have the following query:

SELECT r.cod_reserva, MAX(r.hora_fin_utc) FROM reservas_tanatosalas r GROUP BY r.cod_tanatosala

Results: Is showing the right max value but the wrong cod_reserva. Why?

cod_reserva  MAX(r.hora_fin_utc)
7           9999999999
6           9999999999

What I want to get?:

cod_reserva  MAX(r.hora_fin_utc)
7           9999999999
8           9999999999

UPDATE

cod_reserva could change to varchar in the future hence is not an option to me use max on it.

Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Paul Spiegel, Mar 16 '19 at 13:12

Gordon Linoff · Answer 1 · 2019-03-16T12:45:26.437

Do not use GROUP BY for this. Your query is malformed and won't run in the most recent versions of MySQL (with the default settings) or almost any database.

select r.*
from r
where r.hora_fin_utc = (select max(r2.hora_fin_utc)
                        from reservas_tanatosalas r2
                        where r2.cod_tanatosala = r.cod_tanatosala
                       );

Think about the problem as a filtering problem, not an aggregation problem. You want to filter the data so only the most recent row shows up in the result set.

If performance is an issue, then you want an index on (cod_tanatosala, hora_fin_utc).

This is the your query:

This is the query:

SELECT r.cod_reserva, MAX(r.cod_tanatosala) FROM reservas_tanatosalas r
GROUP BY r.cod_tanatosala
HAVING MAX(r.hora_fin_utc)

This is saying:

produce one row for each value of cod_tanatosala
return the maximum value of cod_tanatosala
ERROR HERE: Don't know what to do with cod_reserva. It is not the argument to an aggregation function or in the GROUP BY.

The HAVING is simply stating that MAX(r.hora_fin_utc) is neither 0 nor NULL. Not very useful.

@GordonLinoff you are right 'having' is useless here. I was confuse trying to pick a row after group by. Thanks for the answer. — Ricardo, Mar 16 '19 at 12:50
@Ricardo check my answer, I found a work around with group by clause — Shoyeb Sheikh, Mar 16 '19 at 13:04

score 0 · Answer 2 · answered Mar 16 '19 at 12:52

You are grouping the resultset using a different column, while your select statement refers to a different one. Following should definitely work, please let me know if it doesn't:

SELECT 
    r.cod_reserva, MAX(r.hora_fin_utc) 
FROM 
    reservas_tanatosalas r 
GROUP BY 
    r.cod_reserva
HAVING 
    MAX(r.hora_fin_utc)

score 0 · Answer 3 · answered Mar 16 '19 at 13:02

I ran the same query as yours,

SELECT MAX(r.cod_reserva), MAX(r.hora_fin_utc) FROM reservas_tanatosalas r GROUP BY r.cod_tanatosala

But I used an aggregate function MAX() on r.cod_reserva as well because without it gives an error "this is incompatible with sql_mode=only_full_group_by" and I got it working, you can test it with MAX(r.cod_reserva).

score 0 · Answer 4 · answered Mar 16 '19 at 13:57

Firstly in most databases including new version of Mysql this code will issue error as you are grouping by a column that is not part of select. Following query will give you what you are after for the dataset you have shown

select (select t.cod_reserva from reservas_tanatosalas t 
        where t.hora_fin_utc = a.max_hora_fin_utc 
        and a.cod_tanatosala = t.cod_tanatosala) cod_reserva, 
        a.max_hora_fin_utc
from (
    SELECT x.cod_tanatosala, MAX(x.hora_fin_utc)max_hora_fin_utc
    FROM reservas_tanatosalas x group by x.cod_tanatosala
)a;

Mysql - Group by and select max value

UPDATE

4 Answers4