We have a Log table which logs the events of a request.
When any error event occurs, I need a query to see the row with errorevent and the previous row to this row which contains the actual data which caused the error.

Here is the table structure:
LOGID (int), --> primary key
EventId (int),
Description (varchar (max))
Logtime (datetime)

Any help is greatly appreciated..

8 Years
Discussion Span
Last Post by pclfw

Give me sample data and what you need to get, then I can understand you well

when I a get a request, we log the event with eventid =1 and request data in the description field
In the next step, we validate field in the the request and log an errorevent with eventid=4 and the error message as description .
Now I want to see the description data (which contains actual request data) from the previous row to the errorevent record.
Here is the sample data:


I am making an assumption or two here, such as LOGID, your primary key, is an IDENTITY column or similar, so try this

SELECT e.logid, e.eventid, e.description, e.logtime,
 l.logid, l.eventid, l.description, l.logtime
FROM logtable e, logtable l
WHERE e.eventid = <event id that you want to examine>
and l.logid = (e.logid - 1)
ORDER by e.logtime DESC, e.logid DESC

Add another AND clause

AND e.logtime = (select max(logtime) from logtable where eventid = <event id that you want to examine>

to get only the latest event records for the relevant event code.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.