select a count of all messages with de date of the latest

Question

Hello I would like to show a list of all messages with the date of the latest message. I've acomplished the list.

SELECT COUNT(*) AS NumberOfTimes, TrackRecord.message_identifier, MessagesInstalledApplications.messageKind FROM TrackRecord INNER JOIN MessagesInstalledApplications ON TrackRecord.message_identifier = MessagesInstalledApplications.message_identifier WHERE TrackRecord.track_record_id NOT IN (SELECT track_record_id FROM TrackRecordLogEntry) AND MessagesInstalledApplications.application_id = 1 GROUP BY TrackRecord.message_identifier, MessagesInstalledApplications.messageKind

This returns:

NumberOfTimes   message_identifier messageKind
1093    4203344 error
176 11558913 error
563 11558912 warning

I would like to add the latest date to that. The date field is: TrackRecord.date_record

can someone help me with that?

Answer 1

So add max(TrackRecord.date_record)?

Be careful with that NOT IN, btw. TrackRecordLogEntry. track_record_id should have a NOT NULL-constraint. Also, I don't think SQL Server's query planner will rewrite that to an anti-join, so you might want to do that yourself.