Edit Question - KnowledgeHub

Title Body

I have a table of player performance: CREATE TABLE TopTen ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, home INT UNSIGNED NOT NULL, `datetime`DATETIME NOT NULL, player VARCHAR(6) NOT NULL, resource INT NOT NULL ); What query will return the rows for each distinct home holding its maximum value of datetime ? In other words, how can I filter by the maximum datetime (grouped by home ) and still include other non-grouped, non-aggregate columns (such as player ) in the result? For this sample data: INSERT INTO TopTen (id, home, `datetime`, player, resource) VALUES (1, 10, '04/03/2009', 'john', 399), (2, 11, '04/03/2009', 'juliet', 244), (5, 12, '04/03/2009', 'borat', 555), (3, 10, '03/03/2009', 'john', 300), (4, 11, '03/03/2009', 'juliet', 200), (6, 12, '03/03/2009', 'borat', 500), (7, 13, '24/12/2008', 'borat', 600), (8, 13, '01/01/2009', 'borat', 700) ; the result should be: id home datetime player resource 1 10 04/03/2009 john 399 2 11 04/03/2009 juliet 244 5 12 04/03/2009 borat 555 8 13 01/01/2009 borat 700 I tried a subquery getting the maximum datetime for each home : -- 1 ..by the MySQL manual: SELECT DISTINCT home, id, datetime AS dt, player, resource FROM TopTen t1 WHERE `datetime` = (SELECT MAX(t2.datetime) FROM TopTen t2 GROUP BY home) GROUP BY `datetime` ORDER BY `datetime` DESC The result-set has 130 rows although database holds 187, indicati

Tags (comma-separated) Cancel