The problem with your query is that when you use GROUP BY
, you have to specify the aggregate function for the fields that are not in the GROUP BY
clause.
You may want to try the following instead:
SELECT u.*
FROM users u
INNER JOIN (
SELECT xName, MAX(xDatetime) max_time
FROM users
GROUP BY xName
) sub_u ON (sub_u.xName = u.xName AND
u.xDateTime = sub_u.max_time);
The above query can be tested as follows:
CREATE TABLE users (id int, xName varchar(100), xDateTime datetime);
INSERT INTO users VALUES (1, 'a', '2010-03-11 00:00:00');
INSERT INTO users VALUES (2, 'a', '2010-03-11 01:00:00');
INSERT INTO users VALUES (3, 'a', '2010-03-11 02:00:00');
INSERT INTO users VALUES (4, 'b', '2010-03-11 01:00:00');
INSERT INTO users VALUES (5, 'b', '2010-03-11 02:00:00');
INSERT INTO users VALUES (6, 'b', '2010-03-11 03:00:00');
INSERT INTO users VALUES (7, 'c', '2010-03-11 06:00:00');
INSERT INTO users VALUES (8, 'c', '2010-03-11 05:00:00');
-- Query Result:
+----+-------+---------------------+
| id | xName | xDateTime |
+----+-------+---------------------+
| 3 | a | 2010-03-11 02:00:00 |
| 6 | b | 2010-03-11 03:00:00 |
| 7 | c | 2010-03-11 06:00:00 |
+----+-------+---------------------+
If you want to order the result-set by the max_time
field, simply add ORDER BY u.xDateTime DESC
at the end of the query.