CODEWITHSUNDEEP
mysql
Antonio Laguna
Antonio Laguna

Reputation: 9282

Is this a MySQL bug or is my query wrong?

Yesterday I run into some sql weirdness. I had a query that melted the server so, trying to improve it, I made this query:

SELECT idEvent, MAX( fechaHora ) , codAgente, evento FROM eventos_centralita GROUP BY codAgente

And it seems to work for this schema:

CREATE TABLE IF NOT EXISTS `eventos_centralita` (
  `idEvent` int(11) NOT NULL AUTO_INCREMENT,
  `fechaHora` datetime NOT NULL,
  `codAgente` varchar(8) DEFAULT NULL,
  `extension` varchar(20) DEFAULT NULL,
  `evento` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idEvent`),
  KEY `codAgente` (`codAgente`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=105847 ;

I mean, that the hour it's indeed the MAX one for the agent. However, the id of the event and the event itself is wrong...

So, is this a bug or is this expected?

Upvotes: 1

Views: 53

Answers (1)

juergen d
juergen d

Reputation: 204746

You are mixing an aggregate function and a "normal" column select. This "feature" only works in MySQL and returns a random id.

Normally you should group by a specific column and the use aggregate functions to select all other columns not in that group. Example:

SELECT e1.codAgente, e1.idEvent, e1.fechaHora, e1.evento
FROM eventos_centralita e1
inner join 
(
    select codAgente, MAX(fechaHora) as fechaHora
    from eventos_centralita
    group by codAgente
) e2 
on e1.codAgente = e2.codAgente and e1.fechaHora = e2.fechaHora

Upvotes: 1

Related Questions