Ad:

MS SQL Discussion Thread
Unsolved
Views: 5297

Jan 22nd, 2009

How to get the previous row for a selection criteria

Expand Post »

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

guest_user

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Jan 2009

Permalink

Jan 22nd, 2009

Re: How to get the previous row for a selection criteria

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

Ramy Mahrous

Featured Poster

Reputation Points: 480

Solved Threads: 276

Postaholic

Offline

2,189 posts
since Aug 2006

Permalink

Jan 22nd, 2009

Re: How to get the previous row for a selection criteria

Click to Expand / Collapse Quote originally posted by RamyMahrous ...

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:

guest_user

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Jan 2009

Permalink

Jan 22nd, 2009

Re: How to get the previous row for a selection criteria

Quote ...

Here is the sample data:

???!

Ramy Mahrous

Featured Poster

Reputation Points: 480

Solved Threads: 276

Postaholic

Offline

2,189 posts
since Aug 2006

Permalink

Jan 23rd, 2009

Re: How to get the previous row for a selection criteria

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

SQL Syntax (Toggle Plain Text)
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
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

SQL Syntax (Toggle Plain Text)
AND e.logtime = (SELECT max(logtime) FROM logtable WHERE eventid = <event id that you want to examine>
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.

Last edited by pclfw; Jan 23rd, 2009 at 9:29 am.

pclfw

Reputation Points: 33

Solved Threads: 9

Junior Poster

Offline

132 posts
since Jun 2005

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Find out why a transaction (DTS) failed

Next Thread in MS SQL Forum Timeline: Copying only the records that don't already exist in destination table.

DaniWeb Message

Cancel Changes

User Name
Password