MySQL "Group By" and "Order By"

Question

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 emailID, fromEmail, subject, timestamp, MIN(read) as read FROM incomingEmails WHERE toUserID = '$userID' GROUP BY LOWER(fromEmail) ORDER BY timestamp DESC

The query almost works as I want it--it selects records grouped by e-mail such that there is only one row per from e-mail address. The problem is that the emailID, subject, and timestamp don't necessarily correspond to the most recent of the e-mails of a particular from e-mail address.

For example, it might return:

From: [email protected] Subject: hello

From: [email protected] Subject: welcome

When the records in the database are:

From: [email protected] Subject: hello

From: [email protected] Subject: programming question

From: [email protected] Subject: welcome

If the programming question e-mail is the most recent, how can I get MySQL to select that record when grouping the e-mails?

Answer 1

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

Answer 2

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.

http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_only_full_group_by

You should first select fromEmail, MIN(read), and then, with second query (or subquery) - Subject.