Description:
I've just tried to follow "Groupwise max" problem solution taken
from the article
http://jan.kneschke.de/projects/mysql/groupwise-max
and faced with a possible malfunction of the 'GROUP BY ... HAVING ...'
method. Though, the 'LEFT JOIN ... ON .. WHERE ... IS NULL' method
works fine, see below.
I know there are many ways to solve the problem but in my case I had
to create a view from the selected rows and it appeared that nested
SELECTs are not allowed in VIEW, so I was forced to use simple JOIN.
Taking into account the problem I report, I see the only solution
with LEFT JOIN which might be too restrictive (e.g. it wouldn't help
in case of AVG() instead of MAX()).
How to repeat:
mysql> CREATE TABLE `record` ( `file` VARCHAR(255), `version` INT );
Query OK, 0 rows affected (0.15 sec)
mysql> INSERT record VALUES ('letter.txt', 1), ('letter.txt', 2), ('message.txt', 1);
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT r1.* FROM record AS r1 JOIN record AS r2 ON r1.file = r2.file GROUP BY file HAVING r1.version = MAX(r2.version);
+-------------+---------+
| file | version |
+-------------+---------+
| message.txt | 1 |
+-------------+---------+
1 row in set (0.04 sec)
-- Oops: there's no row with file='letter.txt' version=2 !?
mysql> SELECT r1.* FROM record AS r1 LEFT JOIN record AS r2 ON r1.file = r2.file AND r1.version < r2.version WHERE r2.file IS NULL;
+-------------+---------+
| file | version |
+-------------+---------+
| letter.txt | 2 |
| message.txt | 1 |
+-------------+---------+
2 rows in set (0.00 sec)
-- Here it is...
This is the table's info:
mysql> SHOW CREATE TABLE `record`;
+--------+-----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------------------------------------------------------------------------------------------+
| record | CREATE TABLE `record` (
`file` varchar(255) default NULL,
`version` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
And this is the MySQL server's version:
$ mysql --version
mysql Ver 14.12 Distrib 5.0.45, for pc-linux-gnu (i686) using readline 5.0
$ rpm -qa | grep MySQL | xargs rpm -qi
Name : MySQL-server Relocations: (not relocatable)
Version : 5.0.45 Vendor: MySQL AB
Release : 0.glibc23 Build Date: Thu 05 Jul 2007 02:09:15 AM MSD
Install Date: Sat 08 Sep 2007 03:08:16 AM MSD Build Host: rhas3-x86.mysql.com
Group : Applications/Databases Source RPM: MySQL-5.0.45-0.glibc23.src.rpm
Size : 32779770 License: GPL
Signature : DSA/SHA1, Tue 10 Jul 2007 08:03:21 PM MSD, Key ID 8c718d3b5072e1f5Packager : MySQL Production Engineering Team <build@mysql.com>
URL : http://www.mysql.com/
Summary : MySQL: a very fast and reliable SQL database server
Description :
The MySQL(TM) software delivers a very fast, multi-threaded, multi-user,
and robust SQL (Structured Query Language) database server. MySQL Server
is intended for mission-critical, heavy-load production systems as well
as for embedding into mass-deployed software. MySQL is a trademark of
MySQL AB.
Copyright (C) 2000-2007 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license.
The MySQL web site (http://www.mysql.com/) provides the latest
news and information about the MySQL software. Also please see the
documentation and the manual for more information.
This package includes the MySQL server binary (incl. InnoDB) as well
as related utilities to run and administrate a MySQL server.
If you want to access and work with the database, you have to install
the package "MySQL-client" as well!
Name : MySQL-client Relocations: (not relocatable)
Version : 5.0.45 Vendor: MySQL AB
Release : 0.glibc23 Build Date: Thu 05 Jul 2007 02:09:15 AM MSD
Install Date: Sat 08 Sep 2007 03:08:10 AM MSD Build Host: rhas3-x86.mysql.com
Group : Applications/Databases Source RPM: MySQL-5.0.45-0.glibc23.src.rpm
Size : 712414 License: GPL
Signature : DSA/SHA1, Tue 10 Jul 2007 07:57:24 PM MSD, Key ID 8c718d3b5072e1f5Packager : MySQL Production Engineering Team <build@mysql.com>
URL : http://www.mysql.com/
Summary : MySQL - Client
Description :
This package contains the standard MySQL clients and administration tools.
For a description of MySQL see the base MySQL RPM or http://www.mysql.com
Name : MySQL-shared Relocations: (not relocatable)
Version : 5.0.45 Vendor: MySQL AB
Release : 0.glibc23 Build Date: Thu 05 Jul 2007 02:09:15 AM MSD
Install Date: Sat 08 Sep 2007 03:08:09 AM MSD Build Host: rhas3-x86.mysql.com
Group : Applications/Databases Source RPM: MySQL-5.0.45-0.glibc23.src.rpm
Size : 3548552 License: GPL
Signature : DSA/SHA1, Tue 10 Jul 2007 08:05:05 PM MSD, Key ID 8c718d3b5072e1f5Packager : MySQL Production Engineering Team <build@mysql.com>
URL : http://www.mysql.com/
Summary : MySQL - Shared libraries
Description :
This package contains the shared libraries (*.so*) which certain
languages and applications need to dynamically load and use MySQL.
Name : MySQL-devel Relocations: (not relocatable)
Version : 5.0.45 Vendor: MySQL AB
Release : 0.glibc23 Build Date: Thu 05 Jul 2007 02:09:15 AM MSD
Install Date: Sat 08 Sep 2007 03:08:12 AM MSD Build Host: rhas3-x86.mysql.com
Group : Applications/Databases Source RPM: MySQL-5.0.45-0.glibc23.src.rpm
Size : 17231601 License: GPL
Signature : DSA/SHA1, Tue 10 Jul 2007 08:02:21 PM MSD, Key ID 8c718d3b5072e1f5Packager : MySQL Production Engineering Team <build@mysql.com>
URL : http://www.mysql.com/
Summary : MySQL - Development header files and libraries
Description :
This package contains the development header files and libraries
necessary to develop MySQL client applications.
For a description of MySQL see the base MySQL RPM or http://www.mysql.com
$ uname -a
Linux localhost.localdomain 2.6.15-1.1831_FC4 #1 Tue Feb 7 13:37:42 EST 2006 i686 athlon i386 GNU/Linux
