Как выбрать строки с MAX(значение колонки), используя PARTITION по другой колонке в MySQL?

Вы на правильном пути! Всё, что вам нужно сделать, это выбрать как home, так и его максимальную дату и время, а затем выполнить соединение с таблицей topten по ОБЕИМ полям:

SELECT tt.*
FROM topten tt
INNER JOIN
    (SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home) groupedtt 
ON tt.home = groupedtt.home 
AND tt.datetime = groupedtt.MaxDateTime

Таким образом, вы получите записи с максимальными значениями даты и времени для каждого home.

Этот запрос будет работать, даже если у вас есть две или более строк для каждого home с одинаковыми значениями DATETIME:

SELECT id, home, datetime, player, resource
FROM   (
       SELECT (
              SELECT  id
              FROM    topten ti
              WHERE   ti.home = t1.home
              ORDER BY
                      ti.datetime DESC
              LIMIT 1
              ) lid
       FROM   (
              SELECT  DISTINCT home
              FROM    topten
              ) t1
       ) ro, topten t2
WHERE  t2.id = ro.lid

В этом запросе используется подзапрос для выбора id из таблицы topten, где для каждого уникального значения home выбирается последняя запись по DATETIME. Подзапросы позволяют получить корректные результаты, даже если для одного и того же home существуют несколько строк с одинаковыми временными метками. В итоге основной запрос извлекает необходимые поля из таблицы topten, связывая их через id.

Я думаю, это поможет вам получить желаемый результат:

SELECT   home, MAX(datetime)
FROM     my_table
GROUP BY home

НО если вам нужны другие столбцы, просто выполните JOIN с оригинальной таблицей (посмотрите ответ Michael La Voie).

С наилучшими пожеланиями.

Да, действительно, ваш запрос намного проще и может работать для вашей задачи. Использование подзапроса с сортировкой, как в вашем примере, позволяет получить последние записи для каждой группы без необходимости использования агрегатных функций.

Вот ваш запрос еще раз:

SELECT * FROM (SELECT * FROM topten ORDER BY datetime DESC) tmp GROUP BY home

Этот запрос сначала сортирует все записи в таблице topten по datetime в порядке убывания, а затем использует GROUP BY, чтобы группировать результаты по полю home. Это обеспечит, что для каждого значения home будет выбрана самая последняя запись на основе времени.

Однако стоит отметить, что SQL-стандарты не всегда гарантируют, что будет возвращена именно первая запись в группе, если не указаны все остальные столбцы в выражении GROUP BY. Поэтому в зависимости от используемой СУБД результат может быть неожиданным. Если вас это беспокоит, возможно, будет лучше использовать подзапрос с ROW_NUMBER() или другие методы, такие как DISTINCT ON (в PostgreSQL), чтобы явно указать, какие записи вы хотите получать.

Тем не менее ваш подход действительно проще и может подойти для многих случаев. Спасибо за его предложение!

Удачи!

Вы можете попробовать следующий запрос, который будет лучше работать для больших таблиц. Он работает, когда для каждого дома не больше двух записей и их даты различны. В качестве более универсального решения для MySQL вы можете использовать запрос, предложенный Майклом Ла Вуа выше:

SELECT t1.id, t1.home, t1.date, t1.player, t1.resource
FROM   t_scores_1 t1 
INNER JOIN t_scores_1 t2
   ON t1.home = t2.home
WHERE t1.date > t2.date

Если вы используете PostgreSQL или базы данных, которые предоставляют аналитические функции, то можно попробовать следующий запрос:

SELECT t.* FROM 
(SELECT t1.id, t1.home, t1.date, t1.player, t1.resource
  , row_number() over (partition by t1.home order by t1.date desc) rw
 FROM   topten t1 
 INNER JOIN topten t2
   ON t1.home = t2.home
 WHERE t1.date > t2.date 
) t
WHERE t.rw = 1

Этот подход позволит вам получить нужные данные более эффективно.