mysql groupby function doesn't work

[sathish_nadu]

I am using MySQL 5.0 on WinXP pro. Below is the script for creating the table

CREATE TABLE `tbl_test` (
  `gameID` VARCHAR(45) NOT NULL,
  `gameName` VARCHAR(100) NOT NULL,
  `gameStartDateTime` DATETIME NOT NULL
);





INSERT INTO `tbl_test`(`gameID`,`gameName`,`gameStartDateTime`) values('gameID1','gameName1','2008-01-01 1:00:00');
INSERT INTO `tbl_test`(`gameID`,`gameName`,`gameStartDateTime`) values('gameID1','gameName1','2008-01-01 8:00:00');
INSERT INTO `tbl_test`(`gameID`,`gameName`,`gameStartDateTime`) values('gameID1','gameName1','2008-01-01 12:00:00');

I would like to get a row with unique gameID + gameName, if there are duplicate, choose the row with the largest gameStartDateTime.

I use the following script to achieve the above objective:

SELECT * FROM  `tbl_test`
GROUP BY gameID, gameName
HAVING MAX(gameStartDateTime);

It doesn't seem to work, since the result is:

'gameID1','gameName1','2008-01-01 1:00:00'

The expected result is

'gameID1','gameName1','2008-01-01 12:00:00'

What is the correct SQL statement?

[Alberto Bucur]

This is correct statment:

SELECT gameID, gameName, MAX(gameStartDateTime) FROM `tbl_test`
GROUP BY gameID, gameName;

SELECT gameID, gameName, gameStartDateTime FROM `tbl_test` GROUP BY gameID, gameName ORDER BY gameStartDateTime DESC;

Not sure if this would give the same result and wehther it would be quicker than the max() or not.