I have a table that keeps tracks of all errors logged for an application I am running. This table includes a column for the date the error occurred, a column for the time, and the error number. There can be several errors with the same error number logged. For example, the table can look like this:
ErrorNum Date Time 245 10/12/2009 01:04:12 244 10/12/2009 09:34:56 245 10/12/2009 15:12:26 244 10/11/2009 23:45:47
I am trying to query a list of each error number and the most recent date and time it occurred. So my results should be:
ErrorNum Date Time 245 10/12/2009 15:12:26 244 10/12/2009 09:34:56
I have tried 2 different queries. The first one is:
select errornum, max(logdate), max(logtime) from log group by errornum
But my results include each error number, and the most current date and the most current time. But it does not neccessarily mean the date and the time are from the same record. It just get the most recent date and the most recent time for each error number.
I also tried this query:
select * from Log Where LogDate = (Select max(LogDate) from Log) And LogTime = (Select max(LogTime) from Log Where LogDate = (Select max(LogDate) from Log))
But my results only include 1 record that ahs the most current date and time and the time is based on the date. The second query works, but I need to get it for all error numbers and not just the one that is the most recent.
Each record has a column called "LogText" which is a description of the error. How can I view the LogText for each of the most recent errors? I tried to just add LogText after the Select statement and I added it in the Group By part too. Let me know if you have any suggestions
I also need to include the errormsg column that is in another table named Errors. Table Errors has a column for the ErrorNum and I need to get the ErrorMsg that matches up with each ErrorNum in the Errors table.
Please let me know if you have any suggestions.