2

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?

Edited by sathish_nadu

3
Contributors
2
Replies
30
Views
3 Years
Discussion Span
Last Post by diafol
1

This is correct statment:

SELECT gameID, gameName, MAX(gameStartDateTime) FROM `tbl_test`
GROUP BY gameID, gameName;
1
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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.