I'm working on a query in MySQL which will give me the latest timestamp for a specific ID but retain it's empty value in the column, something group by makes it very difficult to do.
In my log database I know that if the latest record contains an error message it can be considered "failed" but using group by gives me the latest error message for that message, regardless if it's the latest record.
Select MAX(Timestamp), CountryCode, ErrorMsg, MsgID from messages where
CountryCode = 'AU' group by MsgID;
Timestamp CountryCode ErrorMsg MsgId
2016-04-11 03:10:32 AU Queued for retry 23
2016-04-11 05:23:42 AU Queued for retry 24
2016-04-11 05:50:40 AU 25
2016-04-11 08:19:43 AU 26
2016-04-11 08:40:06 AU Queued for retry 32
2016-04-11 08:40:50 AU Queued for retry 33
I am always tempted to use
where for this:
select m.* from messages m where m.timestamp = (select max(m2.timestamp) from messages m2 where m2.msgid = m.msgid);
For best performance, you want an index on
Note that your query is using a MySQL (mis)feature that allows columns in the
select that are not in the
group by. I discourage you from doing this, unless you really, really, really know what you are doing. In general, this is not part of standard SQL and will not work in other databases (there are very limited cases where this is allowed in the standard and in some other databases).