Когда речь идет о функциях MySQL ORDER BY и GROUP BY, существует порядок действий. Это то, что внутренне понятно, но часто бывает трудно понять, как и когда правильно это применять. В этой статье я попытаюсь объяснить это на примерах.

Для того чтобы сортировать с использованием позднее группировки нам понадобится подзапрос который, создаст так называемую временную таблицу!

Временная таблица создается автоматически всякий раз, когда результаты запроса используются в другом запросе. Этот внутренний запрос называется подзапросом. Таким образом, результаты подзапроса (который автоматически станет временной таблицей) могут быть снова запрошены запросом непосредственно над ним.

Используя приведенный выше запрос для создания отсортированной временной таблицы, окончательный запрос будет следующим:

# MySQL Version <= 5.6
SELECT 
    sorted_temp_table.hole, 
    sorted_temp_table.distance,
    sorted_temp_table.date
FROM (
    SELECT
        hole,
        distance,
        DATE(created_at) AS 'date'
    FROM golf_games
    ORDER BY
        distance DESC,
        hole,
        DATE(created_at)
) AS sorted_temp_table
GROUP BY sorted_temp_table.hole

Чтобы использовать этот запрос в MySQL 5.7 и новее, вам потребуются небольшие изменения:

SELECT
    sorted_temp_table.hole, 
    ANY_VALUE(sorted_temp_table.distance) AS distance,
    ANY_VALUE(sorted_temp_table.date) AS 'date'
FROM (
    SELECT
        (@ROW_NUMBER := @ROW_NUMBER + 1) AS virtual_id,
        hole,
        distance,
        DATE(created_at) AS 'date'
    FROM golf_games,
    (SELECT @ROW_NUMBER := 0) AS row_table
    ORDER BY 
        distance DESC,
        hole,
        DATE(created_at)
) AS sorted_temp_table
GROUP BY sorted_temp_table.hole
ORDER BY sorted_temp_table.hole

В MySQL 8 это немного проще:

# MySQL Version >= 8
SELECT
    sorted_temp_table.hole, 
    ANY_VALUE(sorted_temp_table.distance) AS distance,
    ANY_VALUE(sorted_temp_table.date) AS 'date'
FROM (
    SELECT
        ROW_NUMBER() OVER (
            PARTITION BY hole 
            ORDER BY 
                distance DESC,
                hole,
                DATE(created_at)
        ) AS virtual_id,
        hole,
        distance,
        DATE(created_at) AS 'date'
    FROM golf_games
) AS sorted_temp_table
GROUP BY sorted_temp_table.hole
ORDER BY sorted_temp_table.hole

Важно создать столбец virtual_id для нашей отсортированной временной таблицы. Это позволяет MySQL понять, что это функционально зависимая таблица. Затем, когда MySQL приступит к свертыванию строк на основе нашей GROUP BY sorted_temp_table.hole, предоставленная нами функция ANY_VALUE скажет ему выбрать первую доступную строку для группы.