MySQL Group By и Order By

Простое решение - превратить запрос в подзапрос с помощью оператора ORDER first и применить GROUP BY later:

SELECT * FROM ( 
    SELECT `timestamp`, `fromEmail`, `subject`
    FROM `incomingEmails` 
    ORDER BY `timestamp` DESC
) AS tmp_table GROUP BY LOWER(`fromEmail`)

Это похоже на использование соединения, но выглядит намного лучше.

Использование неагрегированных столбцов в SELECT с предложением GROUP BY является нестандартным. MySQL обычно возвращает значения первой найденной строки и отбрасывает остальные. Любые предложения ORDER BY будут применяться только к возвращаемому значению столбца, а не к отброшенным.

ВАЖНОЕ ОБНОВЛЕНИЕ. Выбор неагрегированных столбцов используется на практике, но на него не следует полагаться. Согласно документации MySQL, это полезно прежде всего, когда все значения в каждом неагрегированном столбце, не названном в GROUP BY, одинаковы для каждой группы. Сервер свободен выбрать любое значение из каждой группы, поэтому если они не совпадают, выбранные значения являются неопределенными.

Начиная с 5.7.5 ONLY_FULL_GROUP_BY включен по умолчанию, поэтому неагрегированные столбцы вызывают ошибки запроса (ER_WRONG_FIELD_WITH_GROUP)

Как указывает @mikep ниже, решением является использование ANY_VALUE () от 5.7 и выше

См. http://www.cafewebmaster.com/mysql-order-sort-group https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value < / а>

Вот один из подходов:

SELECT cur.textID, cur.fromEmail, cur.subject, 
     cur.timestamp, cur.read
FROM incomingEmails cur
LEFT JOIN incomingEmails next
    on cur.fromEmail = next.fromEmail
    and cur.timestamp < next.timestamp
WHERE next.timestamp is null
and cur.toUserID = '$userID' 
ORDER BY LOWER(cur.fromEmail)

Обычно вы присоединяетесь к таблице в поисках более поздних строк. В предложении where вы указываете, что не может быть более поздних строк. Это дает вам только последнюю строку.

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

LEFT JOIN incomingEmails next
    on cur.fromEmail = next.fromEmail
    and cur.id < next.id

Как уже указывалось в ответе, текущий ответ неверен, потому что GROUP BY произвольно выбирает запись из окна.

Если используется MySQL 5.6 или MySQL 5.7 с ONLY_FULL_GROUP_BY, правильный (детерминированный) запрос:

SELECT incomingEmails.*
  FROM (
    SELECT fromEmail, MAX(timestamp) `timestamp`
    FROM incomingEmails
    GROUP BY fromEmail
  ) filtered_incomingEmails
  JOIN incomingEmails USING (fromEmail, timestamp)
GROUP BY fromEmail, timestamp

Для эффективного выполнения запроса требуется правильная индексация.

Обратите внимание, что для упрощения я удалил LOWER(), который в большинстве случаев не будет использоваться.

Сделайте GROUP BY после ORDER BY, заключив свой запрос в GROUP BY следующим образом:

SELECT t.* FROM (SELECT * FROM table ORDER BY time DESC) t GROUP BY t.from

Согласно стандарту SQL вы не можете использовать неагрегированные столбцы в списке выбора. MySQL допускает такое использование (если не используется режим ONLY_FULL_GROUP_BY), но результат непредсказуем.

ONLY_FULL_GROUP_BY

Вы должны сначала выбрать fromEmail, MIN (чтение), а затем, со вторым запросом (или подзапросом) - Subject.

Я боролся с обоими этими подходами для более сложных запросов, чем показано, потому что подход с подзапросом был ужасно неэффективным независимо от того, какие индексы я использовал, и потому что я не мог получить внешнее самосоединение через Hibernate

Лучший (и самый простой) способ сделать это - сгруппировать по чему-то, сконструированному так, чтобы содержать конкатенацию требуемых полей, а затем вытащить их, используя выражения в предложении SELECT. Если вам нужно выполнить MAX (), убедитесь, что поле, которое вы хотите использовать MAX (), всегда находится на самом значительном конце объединенного объекта.

Ключ к пониманию этого заключается в том, что запрос может иметь смысл только в том случае, если эти другие поля инвариантны для любого объекта, который удовлетворяет Max (), поэтому с точки зрения сортировки другие части конкатенации можно игнорировать. Он объясняет, как это сделать, в самом низу этой ссылки. http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

Если вы можете получить событие вставки / обновления (например, триггер) для предварительного вычисления конкатенации полей, вы можете его проиндексировать, и запрос будет таким же быстрым, как если бы группа по занимала только поле, которое вы действительно хотели MAX ( ). Вы даже можете использовать его, чтобы получить максимум нескольких полей. Я использую его для выполнения запросов к многомерным деревьям, выраженным в виде вложенных наборов.