MySQL Group By и Order By

Просто решение е да обвиете заявката в подизбор с командата ORDER първо и прилагане на GROUP BY по-късно:

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

Първо трябва да изберете от Имейл, MIN(четене), а след това, с втората заявка (или подзаявка) - Тема.

Борих се и с двата подхода за по-сложни заявки от показаните, защото подходът на подзаявката беше ужасно неефективен, без значение какви индекси поставих, и защото не можах да получа външното самосъединяване чрез Hibernate

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

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

Ако можете да получите събитие за вмъкване/актуализиране на am (като тригер), за да изчислите предварително конкатенацията на полетата, можете да го индексирате и заявката ще бъде толкова бърза, сякаш групирането по е само над полето, което всъщност искате да МАКСИМАЛНО ( ). Можете дори да го използвате, за да получите максимума от множество полета. Използвам го, за да правя заявки срещу многоизмерни дървета, изразени като вложени набори.