Need help with a Group By clause

[Gabriel Aubut-Lussier]

Hello,

Consider the following table structure:

CREATE TABLE `test` (
`id` bigint(20) NOT NULL auto_increment,
`data` varchar(13) NOT NULL default '',
`name` varchar(25) NOT NULL default '',
`descr` varchar(25) NOT NULL default '',
`version_tag` tinyint(3) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `PUI` (`PUI`)
) TYPE=MyISAM

Consider the following data:

INSERT INTO test(data, name, descr, version_tag) VALUES('001', 'Command 1', 'huhu', 1);
INSERT INTO test(data, name, descr, version_tag) VALUES('002', 'Command 2', 'hehe', 1);
INSERT INTO test(data, name, descr, version_tag) VALUES('003', 'Command 3', 'haha', 1);
INSERT INTO test(data, name, descr, version_tag) VALUES('001', 'Command 1', 'huhuhu', 2);
INSERT INTO test(data, name, descr, version_tag) VALUES('001', 'Command 1', 'DELETED', 3);

Basically, I need to find a way to SELECT the latest Command version for every existing command. Different commands are identified by the value in the data column, and the latest version is the highest number in the version_tag column.

To retrieve the set of commands as of version #2, I have been trying the following command:

SELECT * FROM test WHERE tag <= 2 GROUP BY data ORDER BY tag DESC

but I end up with the rows with ID 2, 3 and 1 instead of the expected: 2, 3 and 4.

Any help would be appreciated! Thanks