klenwell information services : Mysql MAX/MIN from Group

Mysql MAX/MIN from Group

return to DevMysql

This is best illustrated by a simple example. In this example, we have two tables: searches and results. The same search may have different results at different points in time.

In this example, imagine we want to find the most recent result for each of the different results returned by a specific search term.

Code

Create the Two Tables with Simplified Test Data

DROP TABLE IF EXISTS `searches`; 
CREATE TABLE IF NOT EXISTS `searches` (
	id int(11) NOT NULL auto_increment,
	result_id int(11) DEFAULT NULL,
	terms varchar(16) NOT NULL,
	created DATETIME,
	PRIMARY KEY  (`id`),
	KEY `k_result_id` (result_id),
	KEY `k_terms` (terms)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 

INSERT INTO `searches` (result_id, terms, created) VALUES 
 (1, 'hello', NOW()),
 (1, 'hello', NOW() - INTERVAL 1 DAY),
 (2, 'hello', NOW()),
 (2, 'hello', NOW() - INTERVAL 1 DAY);

DROP TABLE IF EXISTS `results`; 
CREATE TABLE IF NOT EXISTS `results` (
	id int(11) NOT NULL auto_increment,
	result varchar(16) NOT NULL,
	created DATETIME,
	PRIMARY KEY  (`id`),
	KEY `k_result` (result)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `results` (id, result, created) VALUES 
 (1, 'world', NOW()),
 (2, 'dolly', NOW());

SELECT Queries for the Results We Want to Find (2 different solutions)
Note: we can easily substitute MIN for MAX

SELECT Search.id, Search.terms, Result.result, Search.created as searched_at
FROM (
  SELECT id, terms, result_id, MAX(created) as created
  FROM searches
  WHERE terms = 'hello'
  GROUP BY terms, result_id
) Search
JOIN results as Result on Result.id = Search.result_id
ORDER BY Search.created DESC
LIMIT 5;

SELECT DISTINCT Search.id, Search.terms, Result.result, Search.created as searched_at
FROM searches Search
JOIN (
  SELECT MAX(created) as created
  FROM searches
  GROUP BY result_id
 ) AS s2
 ON Search.created = s2.created
JOIN results as Result on Result.id = Search.result_id
WHERE Search.terms = 'hello' 
ORDER BY Search.created DESC
LIMIT 5;

Test Results for First Query Above: first with MIN then with MAX
Note: searched_at dates differ by 1 day

mysql> SELECT Search.id, Search.terms, Result.result, Search.created as searched_at
	-> FROM (
	->   SELECT id, terms, result_id, MIN(created) as created
	->   FROM searches
	->   WHERE terms = 'hello'
	->   GROUP BY terms, result_id
	-> ) Search
	-> JOIN results as Result on Result.id = Search.result_id
	-> ORDER BY Search.created DESC
	-> LIMIT 5;
+----+-------+--------+---------------------+
| id | terms | result | searched_at         |
+----+-------+--------+---------------------+
|  1 | hello | world  | 2010-09-29 12:15:39 |
|  3 | hello | dolly  | 2010-09-29 12:15:39 |
+----+-------+--------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT Search.id, Search.terms, Result.result, Search.created as searched_at
	-> FROM (
	->   SELECT id, terms, result_id, MAX(created) as created
	->   FROM searches
	->   WHERE terms = 'hello'
	->   GROUP BY terms, result_id
	-> ) Search
	-> JOIN results as Result on Result.id = Search.result_id
	-> ORDER BY Search.created DESC
	-> LIMIT 5;
+----+-------+--------+---------------------+
| id | terms | result | searched_at         |
+----+-------+--------+---------------------+
|  1 | hello | world  | 2010-09-30 12:15:39 |
|  3 | hello | dolly  | 2010-09-30 12:15:39 |
+----+-------+--------+---------------------+
2 rows in set (0.01 sec)

Query Analysis

mysql> EXPLAIN SELECT Search.id, Search.terms, Result.result, Search.created as searched_at FROM (   SELECT id, terms, result_id, MAX(created) as created   FROM searches   WHERE terms = 'hello'   GROUP BY terms, result_id ) Search JOIN results as Result on Result.id = Search.result_id ORDER BY Search.created DESC LIMIT 5;
+----+-------------+------------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref              | rows | Extra                                        |
+----+-------------+------------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL             |    2 | Using filesort                               |
|  1 | PRIMARY     | Result     | eq_ref | PRIMARY       | PRIMARY | 4       | Search.result_id |    1 |                                              |
|  2 | DERIVED     | searches   | ref    | k_terms       | k_terms | 18      |                  |    3 | Using where; Using temporary; Using filesort |
+----+-------------+------------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+
3 rows in set (0.00 sec)

References

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

klenwell information services : MysqlSelectGroupWiseExtreme

Mysql MAX/MIN from Group

Code

References