CODEWITHSUNDEEP
mysqlsql
Ricardo
Ricardo

Reputation: 8291

Mysql - Group by and select max value

I have the following tables:

reservas_tanatosalas enter image description here

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.

Upvotes: 0

Views: 1498

Answers (4)

Gro
Gro

Reputation: 1683

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;

Upvotes: 0

Shoyeb Sheikh
Shoyeb Sheikh

Reputation: 2866

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).

Upvotes: 0

Pratik Bhavsar
Pratik Bhavsar

Reputation: 848

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)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 1

Related Questions