CODEWITHSUNDEEP
mysqlsql
Wikunia
Wikunia

Reputation: 1592

Group by max(time) mysql

First of all this is kind of a duplicate to: GROUP BY having MAX date I am posting the question because the accepted answer doesn't work for me and I have no idea why. My problem:

I want to select the latest (max(timestamp)) checksum of all functions (func_ids).

The code from @Bill Karwin (accepted answer)

SELECT func_id,checksum
FROM Content cnt 
INNER JOIN (
   SELECT func_id, MAX(timestamp) AS maxdate
   FROM Content GROUP BY func_id
) AS max USING (func_id,maxdate);

Mysql error: #1054 - Unknown column 'maxdate' in 'from clause'

My table:

CREATE TABLE `Content` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `func_id` int(6) NOT NULL,
  `description` text CHARACTER SET utf8 NOT NULL,
  `returns` varchar(255) CHARACTER SET utf8 NOT NULL,
  `var` varchar(255) CHARACTER SET utf8 NOT NULL,
  `content` text CHARACTER SET utf8 NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `checksum` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `func_id` (`func_id`),
  KEY `var` (`var`),
  KEY `checksum` (`checksum`),
  FULLTEXT KEY `description` (`description`)
) ENGINE=MyISAM AUTO_INCREMENT=885 DEFAULT CHARSET=latin1

Upvotes: 2

Views: 821

Answers (3)

Rusty30
Rusty30

Reputation: 161

As I understand the sintaxis from MySQL when you put USING for an Inner Join the columns need to be named the same in both tables. There is no column named maxdate on the content table so the error jumps. You may be able to try (if I understand things correctly) 

SELECT func_id,checksum
FROM Content cnt 
INNER JOIN (
  SELECT func_id, MAX(timestamp) AS maxdate
  FROM Content GROUP BY func_id
) AS max ON (cnt.func_id=max.func_id AND max.maxdate=cnt.timestamp);

Upvotes: 5

Robert Sheahan
Robert Sheahan

Reputation: 2100

when you JOIN ... USING() the argument to USING must be a column or list of columns that appear in both participating tables and match exactly. You don't seem to have a maxdate in Content

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269793

Use an on clause instead of using:

SELECT func_id,checksum
FROM Content cnt INNER JOIN
     (SELECT func_id, MAX(timestamp) AS maxtimestamp
      FROM Content
      GROUP BY func_id
     ) m
     on m.func_id = cnt.func_id and m.maxtimestamp = cnt.timestamp;

Upvotes: 3

Related Questions