I want to be able to select a bunch of rows from a table of e-mails and group them by the from sender. My query looks like this:
SELECT
`timestamp`, `fromEmail`, `subject`
FROM `incomingEmails`
GROUP BY LOWER(`fromEmail`)
ORDER BY `timestamp` DESC
The query almost works as I want it — it selects records grouped by e-mail. The problem is that the subject and timestamp don't correspond to the most recent record for a particular e-mail address.
For example, it might return:
fromEmail: john@example.com, subject: hello
fromEmail: mark@example.com, subject: welcome
When the records in the database are:
fromEmail: john@example.com, subject: hello
fromEmail: john@example.com, subject: programming question
fromEmail: mark@example.com, subject: welcome
If the "programming question" subject is the most recent, how can I get MySQL to select that record when grouping the e-mails?
A simple solution is to wrap the query into a subselect with the ORDER statement first and applying the GROUP BY later:
SELECT * FROM (
SELECT `timestamp`, `fromEmail`, `subject`
FROM `incomingEmails`
ORDER BY `timestamp` DESC
) AS tmp_table GROUP BY LOWER(`fromEmail`)
This is similar to using the join but looks much nicer.
Using non-aggregate columns in a SELECT with a GROUP BY clause is non-standard. MySQL will generally return the values of the first row it finds and discard the rest. Any ORDER BY clauses will only apply to the returned column value, not to the discarded ones.
IMPORTANT UPDATE Selecting non-aggregate columns used to work in practice but should not be relied upon. Per the MySQL documentation "this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate."
As of 5.7.5 ONLY_FULL_GROUP_BY is enabled by default so non-aggregate columns cause query errors (ER_WRONG_FIELD_WITH_GROUP)
As @mikep points out below the solution is to use ANY_VALUE() from 5.7 and above
See 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
As pointed in a reply already, the current answer is wrong, because the GROUP BY arbitrarily selects the record from the window.
If one is using MySQL 5.6, or MySQL 5.7 with ONLY_FULL_GROUP_BY
, the correct (deterministic) query is:
SELECT incomingEmails.*
FROM (
SELECT fromEmail, MAX(timestamp) `timestamp`
FROM incomingEmails
GROUP BY fromEmail
) filtered_incomingEmails
JOIN incomingEmails USING (fromEmail, timestamp)
GROUP BY fromEmail, timestamp
In order for the query to run efficiently, proper indexing is required.
Note that for simplification purposes, I've removed the LOWER()
, which in most cases, won't be used.
order by
in the subselect in the other answers, has no effect at all.
Here's one approach:
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)
Basically, you join the table on itself, searching for later rows. In the where clause you state that there cannot be later rows. This gives you only the latest row.
If there can be multiple emails with the same timestamp, this query would need refining. If there's an incremental ID column in the email table, change the JOIN like:
LEFT JOIN incomingEmails next
on cur.fromEmail = next.fromEmail
and cur.id < next.id
textID
was ambiguous =/
LEFT JOIN
criteria AND next.timestamp <= UNIX_TIMESTAMP()
Do a GROUP BY after the ORDER BY by wrapping your query with the GROUP BY like this:
SELECT t.* FROM (SELECT * FROM table ORDER BY time DESC) t GROUP BY t.from
time
, or the newest time
, or random?
time DESC
and then the group by takes the first one (latest).
According to SQL standard you cannot use non-aggregate columns in select list. MySQL allows such usage (uless ONLY_FULL_GROUP_BY mode used) but result is not predictable.
You should first select fromEmail, MIN(read), and then, with second query (or subquery) - Subject.
I struggled with both these approaches for more complex queries than those shown, because the subquery approach was horribly ineficient no matter what indexes I put on, and because I couldn't get the outer self-join through Hibernate
The best (and easiest) way to do this is to group by something which is constructed to contain a concatenation of the fields you require and then to pull them out using expressions in the SELECT clause. If you need to do a MAX() make sure that the field you want to MAX() over is always at the most significant end of the concatenated entity.
The key to understanding this is that the query can only make sense if these other fields are invariant for any entity which satisfies the Max(), so in terms of the sort the other pieces of the concatenation can be ignored. It explains how to do this at the very bottom of this link. http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html
If you can get am insert/update event (like a trigger) to pre-compute the concatenation of the fields you can index it and the query will be as fast as if the group by was over just the field you actually wanted to MAX(). You can even use it to get the maximum of multiple fields. I use it to do queries against multi-dimensional trees expresssed as nested sets.
Success story sharing
As of 5.7.5 ONLY_FULL_GROUP_BY is enabled by default, i.e. it's impossible to use non-aggregate columns.
SQL mode can be changed during runtime without admin privileges, so it is very easy to disable ONLY_FULL_GROUP_BY. For example:SET SESSION sql_mode = '';
. Demo: db-fiddle.com/f/esww483qFQXbXzJmkHZ8VT/3ORDER BY
is discarded from subqueries, the row selected from the nested query is random. It might work sometimes, adding on to the confusion, but this will result in a nightmare bug. Correct answer is here stackoverflow.com/questions/1066453/mysql-group-by-and-order-by/…