CODEWITHSUNDEEP
mysqlsql
HenrikM
HenrikM

Reputation: 457

SQL - Get latest record for ID and retain empty columns

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;

So I'm trying to select the latest value with MAX(Timestamp) to get the latest record for that message so that I then can check whether or not the ErrorMsg column is empty.

The query above will give the following output;

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

If I look at the messages above which have an error message then their latest has an empty ErrorMsg column.

Is there an alternative to group by which can be used?

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

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 messages(msg_id, timestamp).

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).

Upvotes: 1

Related Questions