SQL - Returning distinct row for data with Max created timestamp value

Question

Within my program, I audit incoming data, which can be of 4 types. If the data meets all required criteria, it gets stored with success in a table column, along with the message type and timestamp of when the row was entered into the table.

Data can also be written to the table with error, due to something like a connection issue occurrring etc with auditing. The program will retry auditing this data, and if succesful will write a new row, with successful. So you see I now have 2 rows for that particular message of data, one having success, one having error, both with different time stamps. (Success having the most recent timestamp than the error record.)

A third message, rejected occurs and has a record written if the incoming data doesnt meet the required standard, again with a create timestamp.

What I'd like to do, is write a Sybase SQL query to pull back only the record for each received message, with the highest timestamp.

So with the above error example, I dont want to return the error record, only the corresponding success record from when the process retried and was a success.

I had thought of something like the following..

SELECT distinct(*) 
    FROM auditingTable
        WHERE timestamp = (SELECT MAX(timestamp) from auditingTable)

though Im aware this will only bring back 1 record, with the highest timestamp in the whole table.

How could I get back the most recent record for each message received, regardless of its status??

Any ideas welcome!

Answer 1

You haven't mentioned your Sybase version. You can use ROW_NUMBER() function

For example your table has MessageId,MessageTime fields you can use following query:

SELECT * FROM
 (
    SELECT auditingTable.*,
    ROW_NUMBER() OVER (PARTITION BY MessageID ORDER BY MessageTime DESC) as RN
    FROM auditingTable 
  ) as T
WHERE RN=1; 

Answer 2

I want to note that a simple modification to your query allows you to do what you want (although I prefer the row_number() method in Valex's answer). That is to turn the subquery in the where clause to a correlated subquery:

SELECT *
FROM auditingTable at1
WHERE timestamp = (SELECT MAX(timestamp)
                   from auditingTable at2
                   where at1.MessageId = at2.MessageId
                  );

This is standard SQL and should work in any version of Sybase.